Show Each Month And Count Or 0 If Null

Question

I am using the below DDL to show sales data for months in 2018 and 2019. My issue is that obviously we will have no data for Sept - Dec 2019, but I need that to be returned and show a 0 amount for it.

How do I need to edit this query so that I have all months 1 - 12 for both 18 and 19 returned?

CREATE TABLE PrevYear (
  [EmployeeNumber] char(8) NOT NULL,
  [SaleAmount] int DEFAULT NULL,
  [SaleDate] date NOT NULL,
  [EmployeeName] char(17) NOT NULL
); 

CREATE TABLE CurrentYear (
  [EmployeeNumber] char(8) NOT NULL,
  [SaleAmount] int DEFAULT NULL,
  [SaleDate] date NOT NULL,
  [EmployeeName] char(17) NOT NULL
); 

INSERT INTO CurrentYear
VALUES ('ea12', '100', '2019-01-10', 'Sam Smith');

INSERT INTO CurrentYear
VALUES ('ea12', '199', '2019-01-13', 'Sam Smith');

INSERT INTO CurrentYear
VALUES ('ea12', '100', '2019-03-01', 'Sam Smith');

INSERT INTO CurrentYear
VALUES ('ls22', '100', '2019-05-01', 'Sam Smith');


INSERT INTO PrevYear
VALUES ('ea12', '100', '2018-01-10', 'Sam Smith');

INSERT INTO PrevYear
VALUES ('ea12', '199', '2018-01-13', 'Sam Smith');

INSERT INTO PrevYear
VALUES ('ea12', '100', '2018-03-01', 'Sam Smith');

INSERT INTO PrevYear
VALUES ('ls22', '100', '2018-05-01', 'Sam Smith');

My desired output from the query is:

Jan 18 $1
Jan 19 $1
Feb 18 $1
Feb 19 $1
Mar 18 $1
Mar 19 $1
Apr 18 $1
Apr 19 $1
May 18 $1
May 19 $1
Jun 18 $1
Jun 19 $1
Jul 18 $1
Jul 19 $1
Aug 18 $1
Aug 19 $1
Sep 18 $1
Sep 19 $0
Oct 18 $1
Oct 19 $0
Nov 18 $1
Nov 19 $0
Dec 18 $1
Dec 19 $0

This is the query I had

SELECT Month, Sum(ia) AS SaleAmount 
FROM   (SELECT Date_format(saledate, '%m-%Y') AS Month, 
           employeename, 
           Sum(SaleAmount)                AS IA 
    FROM   CurrentYear 
    WHERE  employeename = 'Sam Smith' 
    GROUP  BY Date_format(saledate, '%m-%Y'), 
              employeename 
    UNION ALL 
    SELECT Date_format(saledate, '%m-%Y') AS Month, 
           employeename, 
           Sum(SaleAmount)                AS IA 
    FROM   PrevYear
    WHERE  employeename = 'Sam Smith' 
    GROUP  BY Date_format(saledate, '%m-%Y'), 
              employeename) previousQuery 
GROUP  BY month 
ORDER  BY month

You shouldn't use those back ticks, at least for SQL Server. Is this MySQL? If it's SQL Server or you are looking for a SQL Server version, you'll use `CROSS APPLY` — S3S, Aug 06 '19 at 18:16
I wonder, because SQL Server also doesn't have an `Int(11)` datatype. — Tab Alleman, Aug 06 '19 at 18:18
There are hundreds of duplicates of this question. In short, the answer is to JOIN to a date table, that has all the dates you want to show in your results. — Tab Alleman, Aug 06 '19 at 18:21
@TabAlleman & HardCode - I'd like to use this moving forward so should I just create a table that holds the months and excludes the years so I'm not adding in a new year every 12 months? — JamesFisher, Aug 06 '19 at 18:22

score 0 · Accepted Answer · answered Aug 06 '19 at 18:26

You can simply add a lookup table with month that will contains 12 records (one for each month)

The structure will be: id, description.

On existing tables you can refers month by id: less space more performance in join.

On your query using left/right join you will always have the missing month.

PS: need only one table to manage this, and adding a simple where condition (or group by condition) where needed for years management

Show Each Month And Count Or 0 If Null

1 Answers1