Home > front end >  Aggregate multiple invoice numbers and invoice amount rows into one row
Aggregate multiple invoice numbers and invoice amount rows into one row

Time:08-06

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;
  • Related