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?

Solution 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

Solution 2

Subqueries.

select ClientName, 
(select sum(b.quantity) 
from table b where b.clientName = a.clientname 
and b.tradedate = [some constant or calculation that identifies monday])  
as Mon,
(select sum(b.quantity) 
from table b where b.clientName = a.clientname 
and b.tradedate = [some constant or calculation that identifies tuesday])  
as Tue,

..etc..
from table a

A cleaner, but possibly less efficient way involves a view with a group by:ew

create view quantityperday as
select clientname, 
tradedate, 
dayofweek(tradedate) as dow, 
weekofyear(tradedate) as woy, 
year(tradedate) as y,
sum(quantity) as quantity
from table 
group by clientname, tradedate;

Then:

select clientname, b.quantity as Mon, c.quantity as Tue ....
from table a join quantityperday b 
on (a.clientname = b.clientname and b.y = '2008'
and b.doy = 2 and b.dow = 'Monday')
quantityperday c 
on (a.clientname = c.clientname and c.y = '2008'
and c.doy = 2 and c.dow = 'Tuesday')
join ....

The reason this gets ugly is that we're pivoting rows into columns.

Solution 3

Let's see:

SELECT Client, MonSum, TueSum, WedSum, ThuSum, FriSum, SatSum, SunSum, TotSum
    FROM (SELECT ClientName AS Client, SUM(Quantity) AS MonSum
             FROM Trades
             WHERE DayOfWeek(TradeDate) = 'Monday'
               AND TradeDate BETWEEN DATE '..Monday..' AND DATE '..Sunday..'
             GROUP BY ClientName
         ) AS MonData
         JOIN
         (SELECT ClientName AS Client, SUM(Quantity) AS TueSum ...
         ) AS TueData ON Mondata.Client = TueData.Client
         JOIN
         ...
         (SELECT ClientName AS Client, SUM(Quantity) AS TotSum
             FROM Trades
             WHERE TradeDate BETWEEN DATE '..Monday..' AND DATE '..Sunday..'
             GROUP BY ClientName
         ) AS TotData ON MonData.Client = TotData.Client
    ORDER BY Client;

Not tidy, but as @tpdi mentioned in his answer, that's because we're pivoting rows into columns. I've used the consistent TradeDate BETWEEN ... clause to cover the relevant week.

Comments

[Jonathan Leffler]

It is funny - almost every time, people forget to give the table a name. 95% consistent in SQL questions.

[tpdi]

The first table's name is always "a". ;)

[Learning]

OK .. here you go. Not that it makes much of a difference in my opinion. But better to be in 5% :)

[Learning]

However , unlike other answers , it is very pl/sql centric.

[Jeffrey Kemp]

What PL/SQL? That's just SQL.

[Erich Kitzmueller]

Works for you, but not for me. With my NLS settings, days are named "MONTAG", "DIENSTAG", "MITTWOCH" etc. In other words, your solution depends on regional settings.