sqlgoogle-bigqueryaggregatestring-agg

Aggregate multiple invoice numbers and invoice amount rows into one row


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.


Solution

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