CODEWITHSUNDEEP
sqlsql-servergroup-by
goonerboi
goonerboi

Reputation: 319

how to use multiple sum and group by in subquery?

ER DIAGRAM SNAP have to Find the Order Amount Total for 2 type of orders for each year. table is SalesOrderHeader Subtotal + TaxAmt gives the total order amount
OnlineOrderFlag(0/1 is direct/online respectively)

1>Display the results in the below formats

OrderYear         Direct              Online

2001                 75423               344344

2002            
2003        
2004

SQL:

select year(a.OrderDate),

( select  SUM(SubTotal+TaxAmt) FROM SalesOrderHeader b  WHERE OnlineOrderFlag = 0 group by year(b.OrderDate) ) as tot  ,

( select SUM(SubTotal+TaxAmt) FROM SalesOrderHeader c WHERE OnlineOrderFlag = 1 group by year(c.OrderDate) ) as tt

FROM SalesOrderHeader a inner join  SalesOrderHeader b 
on b.SalesOrderID = a.SalesOrderID
            inner join
   SalesOrderHeader c on c.SalesOrderID = a.SalesOrderID

can someone please tel how to proceed further? i'm stuck at this

2> and also how to find it in the below format ?

OnlineFlag               Year                    TotalAmt
Direct                   2001    
Direct                   2002    
Direct                   2003    
Direct                   2004    
Online                   2001    
Online                   2002    
Online                   2003    
Online                   2004

Upvotes: 0

Views: 862

Answers (2)

Jeremy Real
Jeremy Real

Reputation: 766

This should work. Added the inner subquery to make it clear where the group by needs to go and to add the subtotal and tax for each record before summarizing.

select a.year
      ,SUM(Case when a.OnlineOrderFlag = 1 THEN a.total else null end) as Direct
      ,SUM(Case when a.OnlineOrderFlag = 0 THEN a.total else null end) as Online
FROM (select year(OrderDate) as year, (SubTotal+TaxAmt) as total, OnlineOrderFlag
      from SalesOrderHeader) a
Group by a.year

Upvotes: 1

P.Salmon
P.Salmon

Reputation: 17615

SELECT YEAR(ORDERDATE), ONLINEORDERFLAG,
        SUM(SUBTOTAL) SUBTOTAL,SUM(TAXAMT) TAXAMT,
        SUM(SUBTOTAL + TAXAMT) TOTALDUE
FROM SALESORDERHEADER
WHERE ONLINEORDERFLAG = 1
GROUP BY YEAR(ORDERDATE) WITH ROLLUP
UNION
SELECT YEAR(ORDERDATE), ONLINEORDERFLAG,
        SUM(SUBTOTAL) ,SUM(TAXAMT),
        SUM(SUBTOTAL + TAXAMT)
FROM SALESORDERHEADER
WHERE ONLINEORDERFLAG = 0
GROUP BY YEAR(ORDERDATE) WITH ROLLUP
;

Upvotes: 0

Related Questions