I am trying to retrieve user data with this query:
SELECT a.type, SUM(coalesce(a.value, 0)) AS value
FROM app.activity a
WHERE a.user_id = $1 and a.status = $2
GROUP BY a.type
ORDER BY a.type
I tested it on pgAdmin 4 query window, but I'm getting this error:
ERROR: there is no parameter $1
LINE 3: WHERE a.user_id = $1 and a.status = $2
^
SQL state: 42P02
Character: 101
I am trying to get the sum of the user's activity value by grouping.
But I get this error instead:
What's wrong on my side?
To use placeholders, create a prepared statement:
PREPARE mystmt(integer, text) AS
SELECT a.type, SUM(coalesce(a.value, 0)) AS value
FROM app.activity a
WHERE a.user_id = $1 and a.status = $2
GROUP BY a.type
order by a.type
This prepared statement exists until you close the database session (or deallocate the prepared statement), and you can execute it by supplying values for the placeholders with
EXECUTE mystmt(3, 'new');