subtotal using query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

subtotal using query

Author

Topic

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-05 : 13:27:39

i have a table similar to below :


DateDateDate  | field1 | qty1 | qty2 | qty3 
1 July 2012   | abc    | 0    | 35   | 0    
1 July 2012   | xyz    | 2    | 0    | 7
1 July 2012   | def    | 3    | 4    | 1
1 July 2012   | abc    | 41   | 35   | 0  
1 July 2012   | xyz    | 2    | 5    | 7
2 July 2012   | abc    | 0    | 35   | 0    
2 July 2012   | xyz    | 2    | 0    | 7
2 July 2012   | def    | 6    | 4    | 1
2 July 2012   | abc    | 40   | 35   | 0  
3 July 2012   | xyz    | 2    | 15   | 7
3 July 2012   | abc    | 0    | 35   | 0    
3 July 2012   | xyz    | 2    | 0    | 7
3 July 2012   | def    | 6    | 14    | 1
3 July 2012   | abc    | 21   | 15   | 0  
3 July 2012   | xyz    | 2    | 5    | 7

And I'm looking for output similar to below :


field1  | DateDateDate | qty1 | qty2 | qty3 
abc     | 1 July 2012  | 41   | 70   | 0
        | 2 July 2012  | 40   | 70   | 0
        | 3 July 2012  | 21   | 50   | 0
abc     | subtotal     | 102  | 190  | 0
xyz     | 1 July 2012  | 4    |  5   | 14
        | 2 July 2012  | 2    | 0    | 7
        | 3 July 2012  | 6    | 20   | 21
xyz     | subtotal     | 12   | 25   | 42
def     | 1 July 2012  | 3    | 4    | 1
        | 2 July 2012  | 6    | 4    | 1
        | 3 July 2012  | 6    | 14   | 1
 def    |  subtotal    | 15   | 22   | 3
        |  Grand Total |  129 | 137  | 45

Is it possible using query? And I want field1 blank as above before subtotal.

I already tried the query below.


select field1, date, sum(qty1), sum(qty2), sum(qty3)
group by field1, date from table1
union all
select field1, 'subtotal' as date, sum(qty1), sum(qty2), sum(qty3)
group by field1 from table1
union all
select '' as field1, 'Grand Total' as date, sum(qty1), sum(qty2), sum(qty3) from table1

This query displays output something like below and doesn't leave blank field1 before subtotal.


field1  | DateDateDate | qty1 | qty2 | qty3 
abc     | 1 July 2012  | 41   | 70   | 0
abc     | 2 July 2012  | 40   | 70   | 0
abc     | 3 July 2012  | 21   | 50   | 0
abc     | subtotal     | 102  | 190  | 0

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-05 : 14:52:16

Here is teh sample data in a consumable format:

DECLARE @Table1 TABLE (DateDateDate DATE, Field1 VARCHAR(3), qty1 INT, qty2 int, qty3 INT)
INSERT @Table1 VALUES
--DateDateDate  | field1 | qty1 | qty2 | qty3 
('1 July 2012'   , 'abc'    , 0    , 35   , 0),    
('1 July 2012'   , 'xyz'    , 2    , 0    , 7),
('1 July 2012'   , 'def'    , 3    , 4    , 1),
('1 July 2012'   , 'abc'    , 41   , 35   , 0  ),
('1 July 2012'   , 'xyz'    , 2    , 5    , 7),
('2 July 2012'   , 'abc'    , 0    , 35   , 0),    
('2 July 2012'   , 'xyz'    , 2    , 0    , 7),
('2 July 2012'   , 'def'    , 6    , 4    , 1),
('2 July 2012'   , 'abc'    , 40   , 35   , 0 ),
('3 July 2012'   , 'xyz'    , 2    , 15   , 7),
('3 July 2012'   , 'abc'    , 0    , 35   , 0),    
('3 July 2012'   , 'xyz'    , 2    , 0    , 7),
('3 July 2012'   , 'def'    , 6    , 14    , 1),
('3 July 2012'   , 'abc'    , 21   , 15   , 0),  
('3 July 2012'   , 'xyz'    , 2    , 5    , 7)

This is more of a front-end formatting issu. But, here is one way to get the results you want:

SELECT
    CASE WHEN RowNum IN (1, 2) THEN field1 ELSE '' END AS field1,
    DateDateDate,
    qty1,
    qty2,
    qty3
FROM
(
    SELECT 
        field1, 
        CASE 
            WHEN GROUPING(field1) = 1 AND GROUPING(DateDateDate) = 1 THEN 'GrandTotal' 
            WHEN GROUPING(DateDateDate) = 1 THEN 'Subtotal' 
            ELSE CONVERT(VARCHAR(12), DateDateDate, 113) 
        END AS DateDateDate, 
        SUM(qty1) AS qty1, 
        SUM(qty2) AS qty2, 
        SUM(qty3) AS qty3,
        ROW_NUMBER() OVER (PARTITION BY field1 ORDER BY DateDateDate) AS RowNum
    FROM 
        @Table1
    GROUP BY 
        field1, 
        DateDateDate
    WITH ROLLUP
) AS T
ORDER BY
    CASE WHEN field1 IS NULL THEN 0 ELSE 1 END DESC, -- Grand Total last.
    T.Field1,
    T.DateDateDate

learning_grsql
Posting Yak Master

230 Posts

Posted - 2012-09-06 : 08:00:04

@Lamprey...Thank you..
It produces results as I want but it takes 18 seconds to execute. Is it possible to make it faster?
i have heard about index but i never used so far as I'm still new to SQL.
Do you think using index it will make this query faster?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-09-06 : 13:02:25

Does the table you listed above contain all the columns in the table? Are you applying any predicates (filters)? If there are other columns in the table that are not being queried or you are applying filters, then an index might help.

Depending on the answers to my questiosn, you might want to provide the complete DDL of your table. Here are some links that might help you with providing that:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources