I have the following:
budget_id | invoice_number | April | June | August |
---|---|---|---|---|
004 | 11 | NULL | 690 | NULL |
004 | 12 | 1820 | NULL | NULL |
004 | 13 | NULL | NULL | 890 |
What I want to do is do the following:
budget_id | invoice_number | April | June | August |
---|---|---|---|---|
004 | 11, 12, 13 | 1820 | 690 | 890 |
However, when I try to do the following:
SELECT budget_id,
STRING_AGG(invoice_number, ',') AS invoice number,
April,
June,
August
FROM invoice_table
GROUP BY budget_id,
April,
June,
August
Nothing happens. The table stays exactly the same. The code above works if I'm able to comment out the months as it aggregates the invoices numbers without the months. But once I include the months, I still get 3 separate rows. I need the invoice amounts to be included with the months. Is it possible to get the invoice numbers aggregated as well as the invoice amounts in one row? I'm using Big Query if that helps.
CodePudding user response:
Use below query,
SELECT budget_id,
STRING_AGG(invoice_number, ',') invoice_number,
SUM(April) April,
SUM(June) June,
SUM(August) August
FROM invoice_table
GROUP BY 1;