CODEWITHSUNDEEP
sqlsql-server
Vigna Hari Karthik
Vigna Hari Karthik

Reputation: 109

SQL Query with RollUp

this is my query 

select X.Memo, X.Month, Format(sum(X.[Total Sales S$]),'#,0.00') as [Rental Sales $]
    , Format(sum(X.[Net GP S$]),'#,0.00') as [Rental GP $]
from Data X
group by X.Month,X.Memo

This is my result:

enter image description here

Expected result should be like this:

enter image description here

is it possible to do it in SQL? Can help me in the query?

Upvotes: 0

Views: 55

Answers (1)

Avi
Avi

Reputation: 1845

One way would be to use case statement and get the result you mentioned. you can add requird columns in the similar way and get the output.

SELECT MONTH,
  sum(CASE WHEN Memo = 'Rental' THEN sales$ END) Rental_$,
  sum(CASE WHEN Memo = 'Rental' THEN GP$ END) Rental_GP,
  sum(CASE WHEN Memo = 'Sales' THEN sales$ END) Sales_$,
  sum(CASE WHEN Memo = 'Sales' THEN GP$ END) Sales_GP
FROM tablea
GROUP BY MONTH

SELECT MONTH,
  Format(sum(CASE WHEN Memo = 'Rental' THEN (X.[Total Sales S$]) END), '#,0.00') Rental_$,
  Format(sum(CASE WHEN Memo = 'Rental' THEN (X.[Net GP S$]) END), '#,0.00') Rental_GP,
  Format(sum(CASE WHEN Memo = 'Sales' THEN (X.[Total Sales S$]) END), '#,0.00') Sales_$,
  Format(sum(CASE WHEN Memo = 'Sales' THEN (X.[Net GP S$]) END), '#,0.00') Sales_GP,
  Format((sum(CASE WHEN Memo = 'Rental' THEN (X.[Total Sales S$]) END) + sum(CASE WHEN Memo = 'Sales' THEN (X.[Total Sales S$]) END)), '#,0.00') [Total Sales S$],
  Format((sum(CASE WHEN Memo = 'Rental' THEN (X.[Net GP S$]) END) + sum(CASE WHEN Memo = 'Sales' THEN (X.[Net GP S$]) END)), '#,0.00') [Total GP S$]
FROM DATA X
GROUP BY MONTH WITH ROLLUP
ORDER BY SUM(X.MN) ASC

Upvotes: 1

Related Questions