CODEWITHSUNDEEP
mysqlsqljoinaggregate-functions
goonerboi
goonerboi

Reputation: 319

Agggregate of sales...Pivot or transpose?

Have to find the Aggregate Total sales made and Total discount obtained, in year 2002 and also current month of 2002

Table 1 : SalesOrderDetail 

      - UnitPrice
      - UnitPriceDiscount
       - OrderQty

Table 2 : SalesOrderHeader

      - OrderDate

attached is the snap of the schema of those two tables

https://i.sstatic.net/f2b5A.png

I could manage to get the results seperately but was not understanding how to combine/aggregate them....whether to use transpose or pivot...

#Yearly 

select DATE_FORMAT(a.OrderDate,'%Y') AS Yearly,sum(b.UnitPriceDiscount) as DiscTotal,
 SUM((`UnitPrice` * OrderQty)-UnitPriceDiscount) AS total 

FROM SalesOrderHeader a join SalesOrderDetail b 

on b.SalesOrderID = a.SalesOrderID

WHERE YEAR(OrderDate) = 2002 

group by Yearly

order by MONTH(OrderDate)

#CurMonthOfYear

select DATE_FORMAT(a.OrderDate,'%M') AS CurrentMonth,sum(b.UnitPriceDiscount) as DiscountAmt,
 SUM((`UnitPrice` * OrderQty)-UnitPriceDiscount) AS total 

FROM SalesOrderHeader a join SalesOrderDetail b 

on b.SalesOrderID = a.SalesOrderID

 WHERE month(OrderDate) = MONTH( NOW())

** result should be in following format **

  Metrics               Yearly               CurMonthOfYear

DiscountAmt         540.82                            28.48

TotalSales          303.17                     237.2

Upvotes: 0

Views: 51

Answers (2)

Betsy Antony
Betsy Antony

Reputation: 11

select a.Metrics , a.yearly , b.CurMonthOfyear FROM 
(
select 'DiscountAmt' as Metrics , sum(b.UnitPriceDiscount) as yearly
FROM SalesOrderHeader a join SalesOrderDetail b 
on b.SalesOrderID = a.SalesOrderID
WHERE YEAR(OrderDate) = 2002 

union all

select 'TotalSales' as Metrics , SUM((b.UnitPrice * b.OrderQty)-b.UnitPriceDiscount) AS yearly
FROM SalesOrderHeader a join SalesOrderDetail b 
on b.SalesOrderID = a.SalesOrderID
WHERE YEAR(OrderDate) = 2002 

)a

inner join 
(
select 'DiscountAmt' as Metrics , sum(b.UnitPriceDiscount) as CurMonthOfyear
FROM SalesOrderHeader a join SalesOrderDetail b 
on b.SalesOrderID = a.SalesOrderID WHERE month(OrderDate) = MONTH( NOW())

union all

select 'TotalSales' as Metrics , SUM((b.UnitPrice * b.OrderQty)-b.UnitPriceDiscount) AS CurMonthOfyear
FROM SalesOrderHeader a join SalesOrderDetail b 
on b.SalesOrderID = a.SalesOrderID
WHERE month(OrderDate) = MONTH( NOW())
)

b ON a.Metrics = b.Metrics

This is not pivot or transpose, but the query helps to get the output in the required format.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270523

Your question suggests conditional aggregation. However, this is simplest to put into four columns:

select sum(sod.UnitPriceDiscount) as DiscTotal_year,
       sum((UnitPrice * OrderQty) - UnitPriceDiscount) AS total_year,
       sum(case when month(sod.OrderDate) = month(now())
                then sod.UnitPriceDiscount else 0
           end) as DiscTotal_month,
       sum(case when month(sod.OrderDate) = month(now())
                then (UnitPrice * OrderQty) - UnitPriceDiscount else 0
           end) as Total_month
from SalesOrderHeader soh join
     SalesOrderDetail sod b
     on sod.SalesOrderID = soh.SalesOrderID
where year(soh.OrderDate) = 2002 ;

Although you can pivot this to get it on separate rows, that will make the query more complicated.

Upvotes: 1

Related Questions