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
  • 840 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by padtes
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.