Results with Subtotals and Grandtotal

Expand Post »

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

Similar Threads

Clean Previous Next Script for MySQL results in PHP
Showing results of a form in new window in HTML and CSS
Folders open Search Results in Windows NT / 2000 / XP
Web Link freezes - Hijack This results in Viruses, Spyware and other Nasties

RahulV

Reputation Points: 26

Solved Threads: 0

Junior Poster in Training

Offline

92 posts
since Jun 2007

Permalink

Jan 26th, 2010

Re: Results with Subtotals and Grandtotal

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.

MS SQL Syntax (Toggle Plain Text)
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
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:

MS SQL Syntax (Toggle Plain Text)
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
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

apegram

Sponsor

Reputation Points: 318

Solved Threads: 135

LINQ!

Offline

550 posts
since Jan 2010

Permalink

Jan 27th, 2010

Re: Results with Subtotals and Grandtotal

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.

sql Syntax (Toggle Plain Text)
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
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

padtes

Reputation Points: 53

Solved Threads: 20

Junior Poster in Training

Offline

82 posts
since Aug 2009

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: How to specify variable with multiple values

Next Thread in MS SQL Forum Timeline: A Giant Hurdle blocks my Giant Leap!

DaniWeb Message

Cancel Changes

User Name
Password