SQL GROUP BY with LEFT JOIN MS SQL Server

Question

I have a calendar table (c) with a set of 3 month dates:

2021-06-01
2021-07-01
2021-08-01

I have a Stats table (s) with views of each product on the website.

Prod1 | 2021-06-01
Prod1 | 2021-06-01
Prod1 | 2021-08-01
Prod2 | 2021-07-01
Prod2 | 2021-08-01
Prod2 | 2021-08-01

I need to count the views per product per month whether there are views or not.

I have followed many SO answers (SQL - Group By with Left Join) and I cannot see a problem with the code below.

DECLARE @Start date
SET @Start=DATEADD(month, DATEDIFF(month, 0,DATEADD(month, -3, getdate())), 0)

SELECT 
s.ProductID, 
c.themonth,
ISNULL(Count(s.ProductID),0) As Total_Views

FROM 
#calendar c

LEFT JOIN
(
SELECT ProductID,FirstDayOfMonth FROM Stats WHERE FirstDayofMonth >= @Start
) s
ON c.themonth = s.FirstDayOfMonth

GROUP BY 
c.themonth,s.ProductID

ORDER BY s.ProductID,c.themonth

I get results only for ProductIDs that have views in a particular month, instead of a row for each ProductID and each Month whether there are views or not.

With the data above, the result I want is:

Prod1 | 2021-06-01 | 2
Prod1 | 2021-07-01 | 0
Prod1 | 2021-08-01 | 1
Prod2 | 2021-06-01 | 0
Prod2 | 2021-07-01 | 1
Prod2 | 2021-08-01 | 2

Gordon Linoff · Accepted Answer · 2021-09-09 13:15:31Z

2

Use a cross join to generate the rows and then a left join to bring in the data:

select c.themonth, p.productid,
       count(s.productid) as sales_in_month
from #calendar c cross join
     (select distinct productid from stats) p left join
     stats s
     on s.productid = p.productid and
        s.firstdayofmonth = c.themonth
group by c.themonth, p.productid;

answered Sep 9, 2021 at 13:15

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2470281 Over a year ago

Do I need to use a cross join? All the other answers that seems to exactly match my use case don't use a cross join.

user2470281 Over a year ago

If I wanted to get a separate total of enquiries records for the same products and months, how could I add that to your code above? That is, the results should include a total of product views and a total of enquiries.

Gordon Linoff Over a year ago

@user2470281 . . . Can you ask a new question with appropriate sample data, desired results, and a clear explanation?

Collectives™ on Stack Overflow

SQL GROUP BY with LEFT JOIN MS SQL Server

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related