CODEWITHSUNDEEP
sql-servert-sql
fdkgfosfskjdlsjdlkfsf
fdkgfosfskjdlsjdlkfsf

Reputation: 3301

Eliminate count that comes from the left join and not the sales table?

The following query returns the sales per month, regardless if the month is 0. If I group @sales by month then it will return one sale in February and 2 sales in April. This is correct.

Since this will be used as a running total later on, I'll need a count for all months, even if 0. So I do a left join with table 1@months` so that every month is returned regardless of the count.

declare @sales table
(
    salesdate date
)
insert into @sales
select '2/20/2021' union
select '4/15/2021' union
select '4/20/2021'

/* This result is correct: will return two rows: one sale in February and 2 sales in April */
select month(salesdate) as monthnumber, count(*) 'salesTotal' from 
@sales
group by month(salesdate)
order by month(salesdate)




declare @months table 
(
    monthnumber int,
    monthname varchar(10)
)
insert into @months
select 1, 'January' union
select 2, 'February' union
select 3, 'March' union
select 4, 'April' union
select 5, 'May'


select m.monthname, m.monthnumber, count(*)
from @months m left join @sales s on
m.monthnumber = month(salesdate)
group by m.monthname, m.monthnumber

The last query returns the following result:

monthname        monthnumber        TotalSales
January          1                  1
February         2                  1    //This is correct
March            3                  1
April            4                  2    //This is correct
May              5                  1

The problem with this result is that the left join will always return a positive count(*) even if the actual sales count is 0.

How can I exclude the row count if there aren't any sales for the month?

Upvotes: 0

Views: 276

Answers (2)

Charlieface
Charlieface

Reputation: 72040

A much simpler solution than @pwang (which is very useful in some situations), is to just use COUNT on a column.

COUNT will only count non-null values, so we can just specify a column from the sales table, which will be null if there is no join result:

select
   m.monthname,
   m.monthnumber,
   count(salesdate)
from @months m 
left join @sales s 
    on m.monthnumber = month(salesdate)
group by m.monthname, m.monthnumber

The column must be a NOT NULL column, otherwise results may be incorrect.

Upvotes: 0

pwang
pwang

Reputation: 71

Instead of doing COUNT(*), you need an aggregation that will differentiate between non-NULL and NULL values from @sales in the event that the LEFT JOIN does not match because there's no sales in a given month. Try something like this:

select m.monthname, m.monthnumber, sum(case when s.salesdate is not null then 1 else 0 end)
from @months m 
left join @sales s 
    on m.monthnumber = month(salesdate)
group by m.monthname, m.monthnumber

The SUM() in conjunction with the CASE statement will only tally sales that match, as opposed to always counting the single NULL row that is the result of the LEFT JOIN missing.

Upvotes: 1

Related Questions