CODEWITHSUNDEEP
sqlpostgresql
Fetch
Fetch

Reputation: 17

add a list of custom values as an additional column to the end of an SQL query

I have performed a query like this

SELECT date_trunc('month', "submitted_at" AS "submitted_at", count(distinct "user_id") AS "count"
FROM table
GROUP BY date_trunc('month', "submitted_at")
ORDER BY date_trunc('month', "submitted_at") ASC

which returns a table like this (date simplified)

Date         count 
may 2020       7
june 2020      9
july 2020     20
aug 2020      35
sep 2020      89
oct 2020     104

I want to add a 3rd column with custom values like below

Date         count   value 
may 2020       7       9
june 2020      9      17
july 2020     20      38
aug 2020      35      94
sep 2020      89      41
oct 2020     104      78

how do I change my query such that I can add the additional column and get the above table with manually entered values? I've tried the below with no luck. The data is sat on different databases so I can't join the data directly. I'd also need to perform this query without creating any new tables within the database as am going to have to perform a similar query over a wide variety of cases and can't create tables each time I need to do this kind of thing. This is in a postgres database if that's relevant.

SELECT date_trunc('month', "submitted_at" AS "submitted_at", count(distinct "user_id") AS "count", (9,17,38,94,41,78) as "value"
FROM table
GROUP BY date_trunc('month', "submitted_at")
ORDER BY date_trunc('month', "submitted_at") ASC

Upvotes: 0

Views: 990

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I would use a join with values:

SELECT date_trunc('month', "submitted_at") AS "submitted_at", 
       count(distinct "user_id") AS "count", 
       v.val as "value"
FROM table t LEFT JOIN
     (VALUES ('2020-05-01', 9),
             ('2020-06-01', 17),
             . . .
     ) v(mon, val)
     ON date_trunc('month', "submitted_at") = v.mon
GROUP BY date_trunc('month', "submitted_at"), v.val
ORDER BY date_trunc('month', "submitted_at") ASC;

I am making what seems like a reasonable assumption that you want the values aligned by month.

Upvotes: 1

Related Questions