PLSQL : Get sum for each day of week and total sum for week in a single query

Question

Let's say , I have a table, ClientTrade, like thus :

ClientName , TradeDate , Quantity

And I want to create a query in Oracle PLSQL which should return the result like this : (The days are derived from the TradeDate column and Mon = sum(Quantity) for Mon , Tue = sum(Quantity) for Tue ... etc.)

ClientName  Mon Tue Wed Thu Fri Sat Sun TotalForWeek
ABC         10  15  5   2   4   0   0   34
XYZ         1   1   2   1   2   0   0   7 

Assuming that this report will always have where conditions which make it run for one week , is this possible to create this in a single query?

Answer 1

Just simplifying a bit...

SELECT ClientName, 
       SUM(CASE WHEN to_char(TradeDate,'DY')='MON' THEN Quantity ELSE NULL END) AS Mon,
       SUM(CASE WHEN to_char(TradeDate,'DY')='TUE' THEN Quantity ELSE NULL END) AS Tue,
       SUM(CASE WHEN to_char(TradeDate,'DY')='WED' THEN Quantity ELSE NULL END) AS Wed,
       SUM(CASE WHEN to_char(TradeDate,'DY')='THU' THEN Quantity ELSE NULL END) AS Thu,
       SUM(CASE WHEN to_char(TradeDate,'DY')='FRI' THEN Quantity ELSE NULL END) AS Fri,
       SUM(CASE WHEN to_char(TradeDate,'DY')='SAT' THEN Quantity ELSE NULL END) AS Sat,
       SUM(CASE WHEN to_char(TradeDate,'DY')='SUN' THEN Quantity ELSE NULL END) AS Sun,
       SUM(Quantity) AS TotalForWeek
FROM  ClientTrade
GROUP BY ClientName