Member Avatar for RahulV

Hi Friends,
Can there be a query that will SELECT fields of a table and group a column (say "CustomerID") and show all its rows along with its subtotals and then its Grandtotal in the result returned?

For Example,

CustID Items Cost ExtraCharges
---------------------------------------------------------------------------------------
A1 Cofee 15.00 2.00
A1 Tea 10.00 2.00
Subtotal: 25.00 4.00
B1 Cofee 24.00 10.00
B1 Tea 29.00 11.00
Subtotal: 53.00 21.00
GrandTotal: 78.00 25.00

  • 3 Contributors
  • 2 Replies
  • 880 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by padtes

Dani AI

Generated

— good question. 's UNION approach is a practical start and rightly improved the ordering. Two cleaner options to consider: (A) use GROUPING/ROLLUP/GROUPING SETS to emit subtotals and a grand total from a single GROUP BY, or (B) keep the original detail rows exactly and UNION ALL them with the aggregated rows (preferred when you must preserve every raw row). Use GROUPING/GROUPING SETS when you want one query that returns detail-like rows plus aggregates; use UNION ALL when you need the exact source rows unchanged.

Example using GROUPING SETS (produces item-level aggregates, per-customer subtotal and grand total; ordering puts subtotals and grand total after details):

SELECT
  CASE WHEN GROUPING(CustID)=1 THEN NULL ELSE CustID END AS CustID,
  CASE 
    WHEN GROUPING(CustID)=1 THEN 'GrandTotal'
    WHEN GROUPING(Items)=1 THEN 'Subtotal'
    ELSE Items
  END AS Items,
  SUM(Cost)         AS Cost,
  SUM(ExtraCharges) AS ExtraCharges,
  CASE 
    WHEN GROUPING(CustID)=1 THEN 3
    WHEN GROUPING(Items)=1 THEN 2
    ELSE 1
  END AS sort_order
FROM Sales
GROUP BY GROUPING SETS ( (CustID, Items), (CustID), () )
ORDER BY COALESCE(CustID, ''), sort_order, Items;

Notes and troubleshooting tips:

  • GROUPING SETS will collapse rows that have identical grouping values; if you must show every raw row even when duplicates exist, use the UNION ALL pattern (detail rows UNION ALL per-customer aggregates UNION ALL grand total) and add an integer sort key to place subtotals/grand total after each group.
  • Use UNION ALL (not UNION) to avoid a costly DISTINCT pass.
  • Avoid relying on sorting by monetary columns (Cost) to position subtotals; use explicit sort keys or GROUPING() flags so negative values do not misplace totals.
  • For very large tables, pre-aggregate or add covering indexes; or do the presentation in the reporting layer for better formatting and performance.

Recommended Answers

All 2 Replies

Member Avatar for apegram

This is more befitting of a report, but it's mostly doable and could be all doable if someone tweaks it a bit.

The following assumes a Sales table with CustID, Items, Cost, and ExtraCharges fields.

Select CustID, Items, Cost, ExtraCharges
From Sales
Union 
Select CustID, 'Subtotal' as Items, Sum(Cost) as Cost, Sum(ExtraCharges) as ExtraCharges
From Sales
Group By CustID
Union 
Select 'ZZZZZZZZZ' as CustID, 'GrandTotal' as Items, Sum(Cost) as Cost, Sum(ExtraCharges) as ExtraCharges
From Sales
Order By CustID, Cost

Results:

A1	Tea	10.00	2.00
A1	Coffee	15.00	2.00
A1	Subtotal	25.00	4.00
B1	Coffee	24.00	10.00
B1	Tea	29.00	11.00
B1	Subtotal	53.00	21.00
ZZZZZZZZZ	GrandTotal	78.00	25.00
Member Avatar for padtes

The above query by apegram is in general good, except for sort by cost. Typically if there was credit or discount (negative cost). Also grand total shows ugly harcoded custID.
The solution is slight modification to the same, though credit goes to apegram for writing the workable query.

select CustID, Items, Cost, ExtraCharges from (
SELECT '1' orderCol, CustID, Items, Cost, ExtraCharges FROM Sales
Union 
Select '2' orderCol, CustID, 'Subtotal' as Items, Sum(Cost) as Cost, Sum(ExtraCharges) as ExtraCharges
From Sales
Group By CustID
Union 
Select '99' orderCol, '' as CustID, 'GrandTotal' as Items, Sum(Cost) as Cost, Sum(ExtraCharges) as ExtraCharges
From Sales
) as t1
 Order By case when orderCol=99 then 1 else 0 end, CustID, orderCol, items
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.