CODEWITHSUNDEEP
sqlsql-servert-sql
B.Doe12
B.Doe12

Reputation: 23

Getting one row of data pr month even though there is no data for that month

Lets say I have a sales report:

Salesman | Sales | Month  | yr |
    A        5      1       2016
    A        12     1       2017
    A        3      1       2017
    A        25     5       2017
    B        20     4       2017
    B        49     6       2017

If I wanted to see the total sales pr salesman I would do something like this:

SELECT salesman, sum(SALES), MONTH from SALESTABLE GROUP BY Salesman, Mnth   where year = 2017

And get:

Salesman | Sales | Mnth | yr |
   A        15       1    2017
   A        25       5    2017
   B        20       4    2017
   B        49       6    2017

However I want the following:

Salesman | Sales | Mnth | yr   |
   A         15     1     2017
   A         0      2     2017
   A         0      3     2017
   ...
   A         25     5     2017
   ...

I've tried the following:

DECLARE @MonthTable TABLE
(
Monthnumber int
)


DECLARE @cnt int = 0
while @cnt < 12
BEGIN
  SET @cnt = @cnt+1
  Insert into @MonthTable (monthnumber) values (@cnt)
END


SELECT salesman, 
    sum(SALES), 
    monthnumber 
FROM SALESTABLE
RIGHT JOIN @MonthTable ON monthnumber = mnth    
GROUP BY Salesman, monthnumber

This however gives a NULL on "Salesman", as expected.

Upvotes: 2

Views: 85

Answers (2)

SqlZim
SqlZim

Reputation: 38023

By generating a list of months and cross joining a distinct list of salesmen and year to join back to the table, we can group and sum by the generated months like so:

;with months as (
  select Mnth 
  from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12)) t(Mnth)
)
select 
    s.Salesman
  , s.Yr
  , m.Mnth
  , Sales = coalesce(sum(t.Sales),0)
from (
  select distinct Salesman, yr
  from t
  ) as s
  cross join months m
  left join t
    on s.Salesman = t.Salesman
    and s.Yr = t.Yr
    and m.Mnth = t.[Month]
where s.yr = 2017
group by s.Salesman, s.Yr, m.Mnth

rextester demo: http://rextester.com/ZOHB53144

returns:

+----------+------+------+-------+
| Salesman |  Yr  | Mnth | Sales |
+----------+------+------+-------+
| A        | 2017 |    1 |    15 |
| A        | 2017 |    2 |     0 |
| A        | 2017 |    3 |     0 |
| A        | 2017 |    4 |     0 |
| A        | 2017 |    5 |    25 |
| A        | 2017 |    6 |     0 |
| A        | 2017 |    7 |     0 |
| A        | 2017 |    8 |     0 |
| A        | 2017 |    9 |     0 |
| A        | 2017 |   10 |     0 |
| A        | 2017 |   11 |     0 |
| A        | 2017 |   12 |     0 |
| B        | 2017 |    1 |     0 |
| B        | 2017 |    2 |     0 |
| B        | 2017 |    3 |     0 |
| B        | 2017 |    4 |    20 |
| B        | 2017 |    5 |     0 |
| B        | 2017 |    6 |    49 |
| B        | 2017 |    7 |     0 |
| B        | 2017 |    8 |     0 |
| B        | 2017 |    9 |     0 |
| B        | 2017 |   10 |     0 |
| B        | 2017 |   11 |     0 |
| B        | 2017 |   12 |     0 |
+----------+------+------+-------+

Upvotes: 3

Phylyp
Phylyp

Reputation: 1689

Just as you've rightly created a month table to provide month entries for those months that don't have sales, you need a master salesman table to give salesmen information for those months that don't have sales.

In its simplest form, you can generate a master salesman table from the salesdata table by getting distinct salesman; ideally you might wish to use a separate salesman table that has one entry per salesperson.

The query then becomes: 

SELECT salesman, 
    sum(SALES), 
    monthnumber 
FROM SALESTABLE
RIGHT JOIN @MonthTable ON monthnumber = mnth    
GROUP BY Salesman, monthnumber

SELECT 
    salesmanlist.salesman 
  , SUM(salestable.sales) 
  , monthtable.monthnumber 
FROM 
  (SELECT DISTINCT salesman FROM salestable) as salesmanlist   --   <-- Key change 
  CROSS JOIN @MonthTable as monthtable 
  LEFT JOIN salestable 
    ON     salesmanlist.salesman = salestable.salesman 
       AND salestable.mnth = monthtable.monthnumber

Upvotes: 0

Related Questions