Calculating sub-total in each group

Question

I need report from multi table I use this query (SQL Server)

Select  CASE When ([bills].[BT] ='0' and [bills].[T] = 1 )Then 'Purchas1'
When([bills].[BT] ='0' and [bills].[T] = 3 ) Then 'Output'
When([bills].[BT] ='0' and [bills].[T] = 4 ) Then 'Input' 
When [bills].[BT] ='1'  Then  'ٍSales'
When [bills].[BT] = '2' Then  'Prch2'
When [bills].[BT] = '3' Then  'ٍSales2'
When [bills].[BT] = '4'  Then 'SInput' 
END AS BillType,
[mat].[Name] as Product,
[mat].[Code], [store].[Name], 
SUM( [billInfo].[qty]) as Qtys 
from [mat],[billInfo000],[store],[bu],[bills] 
Where [bu].[TG] =[bills].[g]
and [billInfo].[ParentGUID] =[bu].[g] 
and [billInfo].[StoreGUID] =[store].[g] 
and [billInfo].[MatGUID] = [mat].[g] 
Group by [bills].[BT],[bills].[T],[mat].[Name], 
[mat].[Code],[store].[Name] ,[mat].[qty]

what i want is adding one row after each group with Calculating If it's same Product and same code and store i need collect purchase1+Input+Prch2+SInput minus Sales, Output, Sales2 Like This:

BillType | Product | Code | Name | Qtys 
--------   -------   ----   ----   ---- 
Purchas1   Pro1      001    Main    150
Output     Pro1      001    Main    10
Sales      Pro1      001    Main    30
Purch2     Pro1      001    Main    50
Balance    Pro1      001    Main    160
Output     Pro1      001    Branch  10
Sales      Pro1      001    Branch  10
Balance    Pro1      001    Brabch  -20

Thanks

What implementation of SQL? SQL Server? Oracle? MySQL? PostgreSQL? — JNK, Aug 08 '11 at 20:14
Do you require this to be done in a single SQL Statement? I don't think it's possible but if it is, I'm interested in seeing that wonderful sql statement. Doing it in a stored proc should be easy, though. — Icarus, Aug 08 '11 at 20:26
@Icarus - I think it may be possible with a `ROLLUP` but I haven't played with them much — JNK, Aug 08 '11 at 20:28
@JNK thanks for the hint. I'll look it up. Didn't know about it. :) — Icarus, Aug 08 '11 at 20:30
@Icarus it won't work for this exact scenario since he has some custom logic, but `ROLLUP` will total a `GROUP BY` for all groups basically — JNK, Aug 08 '11 at 20:35
It appears that if this is a SQL Server query, all @Mohammed needs to do is add a WITH ROLLUP clause after the Group BY. See this example: http://blog.sqlauthority.com/2010/02/24/sql-server-introduction-to-rollup-clause/ — Icarus, Aug 08 '11 at 20:46

Lieven Keersmaekers · Accepted Answer · 2011-08-10T10:37:09.977

Not a ROLLUP but using a WITH statement and a UNION could do the trick just as wel.

The gist of it is to

Store your original query in q using the WITH statement
SELECT all from q
Further refine the GROUP BY again from qto calculate the balances
UNION the results together

SQL Server 2000

SELECT  *
FROM    (
          SELECT  CASE  WHEN ([bills].[BT] ='0' and [bills].[T] = 1 ) THEN 'Purchas1'
                        WHEN ([bills].[BT] ='0' and [bills].[T] = 3 ) THEN 'Output'
                        WHEN ([bills].[BT] ='0' and [bills].[T] = 4 ) THEN 'Input' 
                        WHEN [bills].[BT] = '1' THEN 'Sales'
                        WHEN [bills].[BT] = '2' THEN 'Prch2'
                        WHEN [bills].[BT] = '3' THEN 'Sales2'
                        WHEN [bills].[BT] = '4' THEN 'SInput' 
                  END AS BillType
                  , [mat].[Name] AS Product
                  , [mat].[Code]
                  , [store].[Name]
                  , SUM([billInfo].[qty]) AS Qtys 
          FROM    [mat]
                  INNER JOIN [billInfo000] ON [billInfo000].[MatGUID] = [mat].[g]
                  INNER JOIN [store] ON [store].[g] = [billInfo0001].[StoreGUID]
                  INNER JOIN [bu] ON [bu].[g] = [billInfo000].[ParentGUID]
                  INNER JOIN [bills] ON [bills].[g] = [bu].[TG]
          GROUP BY
                [bills].[BT]
                , [bills].[T]
                , [mat].[Name]
                , [mat].[Code]
                , [store].[Name]
                , [mat].[qty]
        ) bt
UNION ALL
SELECT  'Balance'
        , Product
        , Code
        , Name
        , SUM(
            CASE  WHEN BillType = 'Purchas1' THEN Qtys
                  WHEN BillType = 'Output' THEN Qtys * -1
                  WHEN BillType = 'Sales' THEN Qtys * -1
                  WHEN BillType = 'Purch2' THEN Qtys
            END)
FROM    (
          SELECT  CASE  WHEN ([bills].[BT] ='0' and [bills].[T] = 1 ) THEN 'Purchas1'
                        WHEN ([bills].[BT] ='0' and [bills].[T] = 3 ) THEN 'Output'
                        WHEN ([bills].[BT] ='0' and [bills].[T] = 4 ) THEN 'Input' 
                        WHEN [bills].[BT] = '1' THEN 'Sales'
                        WHEN [bills].[BT] = '2' THEN 'Prch2'
                        WHEN [bills].[BT] = '3' THEN 'Sales2'
                        WHEN [bills].[BT] = '4' THEN 'SInput' 
                  END AS BillType
                  , [mat].[Name] AS Product
                  , [mat].[Code]
                  , [store].[Name]
                  , SUM([billInfo].[qty]) AS Qtys 
          FROM    [mat]
                  INNER JOIN [billInfo000] ON [billInfo000].[MatGUID] = [mat].[g]
                  INNER JOIN [store] ON [store].[g] = [billInfo0001].[StoreGUID]
                  INNER JOIN [bu] ON [bu].[g] = [billInfo000].[ParentGUID]
                  INNER JOIN [bills] ON [bills].[g] = [bu].[TG]
          GROUP BY
                [bills].[BT]
                , [bills].[T]
                , [mat].[Name]
                , [mat].[Code]
                , [store].[Name]
                , [mat].[qty]
        ) balance
GROUP BY
        Product
        , Code
        , Name

SQL Server 2005+

;WITH q AS (
  SELECT  CASE  WHEN ([bills].[BT] ='0' and [bills].[T] = 1 ) THEN 'Purchas1'
                WHEN ([bills].[BT] ='0' and [bills].[T] = 3 ) THEN 'Output'
                WHEN ([bills].[BT] ='0' and [bills].[T] = 4 ) THEN 'Input' 
                WHEN [bills].[BT] = '1' THEN 'Sales'
                WHEN [bills].[BT] = '2' THEN 'Prch2'
                WHEN [bills].[BT] = '3' THEN 'Sales2'
                WHEN [bills].[BT] = '4' THEN 'SInput' 
          END AS BillType
          , [mat].[Name] AS Product
          , [mat].[Code]
          , [store].[Name]
          , SUM([billInfo].[qty]) AS Qtys 
  FROM    [mat]
          INNER JOIN [billInfo000] ON [billInfo000].[MatGUID] = [mat].[g]
          INNER JOIN [store] ON [store].[g] = [billInfo0001].[StoreGUID]
          INNER JOIN [bu] ON [bu].[g] = [billInfo000].[ParentGUID]
          INNER JOIN [bills] ON [bills].[g] = [bu].[TG]
  GROUP BY
        [bills].[BT]
        , [bills].[T]
        , [mat].[Name]
        , [mat].[Code]
        , [store].[Name]
        , [mat].[qty]
)
SELECT  *
FROM    q
UNION ALL
SELECT  'Balance'
        , Product
        , Code
        , Name
        , SUM(
            CASE  WHEN BillType = 'Purchas1' THEN Qtys
                  WHEN BillType = 'Output' THEN Qtys * -1
                  WHEN BillType = 'Sales' THEN Qtys * -1
                  WHEN BillType = 'Purch2' THEN Qtys
            END)
FROM    q
GROUP BY
        Product
        , Code
        , Name

Thanks @Lieven, It's seems work well in my PC But when I tried to Test it on the client Server, which works on SQL-Server 2000 :( I face problem With "With" What should I do to make it work in both SQL-Server 2000 and 2008 ? — Mohammed Rabee, Aug 09 '11 at 21:28
@Mohammed Rabee - WITH is SQL Server 2005 +. To have it work for SQL Server 2000, all you need to do is replace every instance of `q`(two of them) with the `SELECT` statement inside `WITH` (and drop `WITH`offcourse) — Lieven Keersmaekers, Aug 10 '11 at 00:12
@Mohammed Rabee - I have added the query for SQL Server 2000. — Lieven Keersmaekers, Aug 10 '11 at 10:37

Calculating sub-total in each group

1 Answers1

SQL Server 2000

SQL Server 2005+