If you have a look at the heading on the page (at the bottom) “Right way of grouping by month in SQL Server” It only returns a result list for months that have units sold. but how about including all months… e.g.
Year Month Units sold
----------- ----------- ---------------------------------------
[B]2004 1 0.00[/B]
2004 2 6.00
2004 3 5.00
After doing some googling i read that I need to join to another table containing a list of all the months? But do I also need a table with all the years?
If you want to do it in pure SQL, then yes, creating those dummy tables is an easy solution.
However, why do you want to do it in SQL? You can easily handle displaying the 0’s in your application. The application is where you format output, not in database queries.
You would have a table of months use the appropriate join (left or right) to your result set, using some function such as IsNull() or Coalesce() (depending on the DBMS) to convert nulls into 0s.
CREATE TABLE numbers ( n INTEGER NOT NULL PRIMARY KEY);
INSERT INTO numbers VALUES (0),(1),(2),(3),(4),(5),...
make sure you have enough numbers to cover the largest range of year/months
now you can write a query like this –
SELECT '2004-01-01' + INTERVAL n MONTH AS mydate
FROM numbers
WHERE '2004-01-01' + INTERVAL n MONTH <= '2010-01-01
or like this –
SELECT EXTRACT(YEAR_MONTH FROM mydate) AS yyyymm
FROM ( SELECT '2004-01-01' + INTERVAL n MONTH AS mydate
FROM numbers
WHERE '2004-01-01' + INTERVAL n MONTH <= '2010-01-01 ) AS d
SELECT EXTRACT(YEAR_MONTH FROM mydate) AS yyyymm
, SUM(sales.sales_qty) AS units_sold
FROM ( SELECT '2004-01-01' + INTERVAL n MONTH AS mydate
FROM numbers
WHERE '2004-01-01' + INTERVAL n MONTH <= '2010-01-01' ) AS d
LEFT OUTER
JOIN sales
ON sales.sales_date >= d.mydate
AND sales.sales_date < d.mydate + INTERVAL 1 MONTH
GROUP
BY yyyymm
