How to aggregate totals by customer ID while grouping SQL results

Data and Databases
1

I’m working on a SQL query that pulls data from two joined tables but I’m getting multiple rows for the same order that I need to combine.

Here’s what I have so far:

-- Monthly invoice summary report
SELECT 
    invoices.INVOICE_NUM, invoices.INVOICE_DATE,   
    invoices.ORDER_REF, invoice_items.QTY_DELIVERED, 
    invoice_items.PRICE_PER_UNIT, 
    invoice_items.QTY_DELIVERED * invoice_items.PRICE_PER_UNIT AS LINE_TOTAL
FROM
    invoices
INNER JOIN 
    invoice_items on invoices.INVOICE_NUM = invoice_items.INVOICE_NUM
WHERE
    invoices.INVOICE_DATE >= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
    AND invoices.INVOICE_DATE < DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)
ORDER BY 
    invoices.INVOICE_DATE

This gives me results like:

INVOICE_NUM  DATE        ORDER_REF  LINE_TOTAL
-------------------------------------------------
12345        2024-10-01  ORD001     850.00
12346        2024-10-01  ORD002     275.00
12347        2024-10-01  ORD003     420.00
12347        2024-10-01  ORD003     1150.00
12347        2024-10-01  ORD003     0.00
12348        2024-10-04  ORD004     650.00

But I want to get the combined totals for each ORDER_REF like this:

INVOICE_NUM  DATE        ORDER_REF  LINE_TOTAL
-------------------------------------------------
12345        2024-10-01  ORD001     850.00
12346        2024-10-01  ORD002     275.00
12347        2024-10-01  ORD003     1570.00
12348        2024-10-04  ORD004     650.00

What’s the right way to sum up the LINE_TOTAL values for each ORDER_REF while keeping the rows grouped by INVOICE_NUM? Sometimes the same ORDER_REF appears with different INVOICE_NUM values and those should stay as separate rows.

2

Interesting case! Are you seeing this pattern across other months too? Those zero values in line_total caught my attention - are they expected or could there be a data quality issue causing the duplication? What’s actually showing up in the invoice_items table for invoice 12347?

3

Yeah, the GROUP BY approach works, but you’ve got to include all non-aggregated columns in your GROUP BY clause. Right now your query’s returning multiple rows because each invoice line item creates its own row. When you wrap SUM() around your line total calculation, SQL Server makes you group by INVOICE_NUM, INVOICE_DATE, and ORDER_REF to get the invoice-level totals. This smashes all line items from the same invoice into one row with the total amount. I’ve hit this same issue building reporting queries - you’re basically going from line-item detail to invoice summary data.

4

wrap your line_total calculation in sum() and add a group by clause:

SELECT invoices.INVOICE_NUM, invoices.INVOICE_DATE, invoices.ORDER_REF, SUM(invoice_items.QTY_DELIVERED * invoice_items.PRICE_PER_UNIT) AS LINE_TOTAL FROM invoices INNER JOIN invoice_items on invoices.INVOICE_NUM = invoice_items.INVOICE_NUM WHERE [your date conditions] GROUP BY invoices.INVOICE_NUM, invoices.INVOICE_DATE, invoices.ORDER_REF ORDER BY invoices.INVOICE_DATE

that’ll fix it.