CODEWITHSUNDEEP
sqlsql-serverpivot
techora
techora

Reputation: 619

Create query to display number of products sold for each day of the week

I have am trying to generate a table that looks like this in SQL Server: 

Name     | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Total
Product1 | 2   | 0   | 1   | 0   | 1   | 0   | 0   | 4
Product2 | 2   | 1   | 0   | 1   | 0   | 0   | 0   | 4
Product3 | 1   | 0   | 0   | 0   | 0   | 0   | 0   | 1

I have my data currently sitting in three tables like the ones below:

ProductName
ID | Name             
1  | Product1    
2  | Product2   
3  | Product3      

ProductSelection
PID | CID
1   | 1
1   | 2
1   | 3
2   | 1
2   | 2
3   | 2
4   | 1
5   | 2
6   | 1

ProductOrder 
ID | Name     | SubmitDate
1  | John Doe | 6/15/2015       
2  | Jane Doe | 6/15/2015 
3  | Sam Doe  | 6/16/2015 
4  | Mark Doe | 6/17/2015 
5  | Bob Doe  | 6/18/2015
6  | Adam Doe | 6/19/2015

Now I know I need to use a PIVOT similar to this:

PIVOT (COUNT(Name) FOR [Day] IN (Mon, Tue, Wed, Thu, Fri, Sat, Sun)) AS PivotTable

But I am struggling on putting the rest of the query together. I don't know how to get the total either, perhaps a INNER JOIN? Any assistance would be greatly appreciated.

Upvotes: 0

Views: 866

Answers (2)

Felix Pamittan
Felix Pamittan

Reputation: 31879

Aside from using PIVOT, you can try using conditional aggregation:

SQL Fiddle

SELECT
    Name    = pn.Name,  
    [Mon]   = SUM(CASE WHEN DATENAME(WEEKDAY, po.SubmitDate) = 'Monday' THEN 1 ELSE 0 END),
    [Tue]   = SUM(CASE WHEN DATENAME(WEEKDAY, po.SubmitDate) = 'Tuesday' THEN 1 ELSE 0 END),
    [Wed]   = SUM(CASE WHEN DATENAME(WEEKDAY, po.SubmitDate) = 'Wednesday' THEN 1 ELSE 0 END),
    [Thu]   = SUM(CASE WHEN DATENAME(WEEKDAY, po.SubmitDate) = 'Thursday' THEN 1 ELSE 0 END),
    [Fri]   = SUM(CASE WHEN DATENAME(WEEKDAY, po.SubmitDate) = 'Friday' THEN 1 ELSE 0 END),
    [Sat]   = SUM(CASE WHEN DATENAME(WEEKDAY, po.SubmitDate) = 'Saturday' THEN 1 ELSE 0 END),
    [Sun]   = SUM(CASE WHEN DATENAME(WEEKDAY, po.SubmitDate) = 'Sunday' THEN 1 ELSE 0 END),
    [Total] = COUNT(*)
FROM ProductOrder po
INNER JOIN ProductSelection ps 
    ON ps.PID = po.ID
INNER JOIN ProductName pn
    ON pn.ID = ps.CID
GROUP BY pn.Name

RESULT

|     Name | Mon | Tue | Wed | Thu | Fri | Sat | Sun | Total |
|----------|-----|-----|-----|-----|-----|-----|-----|-------|
| Product1 |   2 |   0 |   1 |   0 |   1 |   0 |   0 |     4 |
| Product2 |   2 |   1 |   0 |   1 |   0 |   0 |   0 |     4 |
| Product3 |   1 |   0 |   0 |   0 |   0 |   0 |   0 |     1 |

Upvotes: 1

Simon Wang
Simon Wang

Reputation: 2943

Try this:

select ProductName.Name, COUNT(ID), datename(dw,SubmitDate)  
from ProductOrder
    inner join ProductName on ProductOrder.ID = ProductName.ID
group by  ProductName.Name, datename(dw, SubmitDate)

You will get all counts of each product sell count in each day of week

Upvotes: 0

Related Questions