Subtotal of columns with multiple case statements

Hello,

Below is an SQL statement where I am pulling sales history for multiple weeks and placing them in columns. I would like to subtotal each column. Attached is the code...

The COMPUTE statement does not create the subtotal at the bottom of the query results...the query runs correctly besides that.

Code Snippet:

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:

           
           
             SELECT CustomerType
       ,Sum(Week0) AS [Week of 1 Mar]
       ,Sum(Week1) AS [Week of 8 Mar]
       ,Sum(Week2) AS [Week of 15 Mar]
  FROM (
    SELECT CustomerType
       , CASE
           WHEN i.InvoiceDate >= '03/02/2008' and i.InvoiceDate < '03/09/2008'
             THEN (id.amount)
           ELSE 0
         END AS [Week0]
       , CASE
           WHEN InvoiceDate >= '03/09/2008' and InvoiceDate < '03/16/2008'
             THEN (id.Amount)
           ELSE 0
         END AS [Week1]
       , CASE
           WHEN InvoiceDate >= '03/16/2008' and InvoiceDate < '03/23/2008'
             THEN (id.Amount)
           ELSE 0
         END AS [Week2]
      FROM tbInvoice i
        INNER JOIN [tbInvoiceDetail] id ON i.guidInvoice = id.guidInvoice
        INNER JOIN tbCustomer c on i.guidcustomer = c.guidcustomer
        INNER JOIN tbCustomerTypeInfo ct on c.guidcustomertype = ct.guidcustomertype
      WHERE (i.InvoiceDate >= '03/01/2008' AND i.InvoiceDate < '03/30/2008') and id.componentlevel = 0
       ) AS InnerDetail
 GROUP BY customertype
 Order by customertype
 COMPUTE Sum(Sum(Week0)), Sum(Sum(Week1)), sum(sum(week2))

           
         

Open in New Window

Select All

Solution: Subtotal of columns with multiple case statements

SELECT * FROM
(
SELECT CustomerType
, Sum(CASE
WHEN i.InvoiceDate >= '03/02/2008' and i.InvoiceDate < '03/09/2008'
THEN (id.amount)
ELSE 0
END) AS [Week of 1 Mar]
, sum(CASE
WHEN InvoiceDate >= '03/09/2008' and InvoiceDate < '03/16/2008'
THEN (id.Amount)
ELSE 0
END) AS [Week of 8 Mar]
, sum( CASE
WHEN InvoiceDate >= '03/16/2008' and InvoiceDate < '03/23/2008'
THEN (id.Amount)
ELSE 0
END) AS [Week of 15 Mar]
FROM tbInvoice i
INNER JOIN [tbInvoiceDetail] id ON i.guidInvoice = id.guidInvoice
INNER JOIN tbCustomer c on i.guidcustomer = c.guidcustomer
INNER JOIN tbCustomerTypeInfo ct on c.guidcustomertype = ct.guidcustomertype
WHERE i.InvoiceDate >= '03/01/2008' AND i.InvoiceDate < '03/30/2008')
and id.componentlevel = 0
GROUP BY customertype
Order by customertype
) AS InnerDetail
COMPUTE Sum([Week of 1 Mar])
, Sum([Week of 8 Mar])
, sum([Week of 15 Mar])