Group by all months

[chumba]

Hi

Can anybody expand on this code to include zero's for the missing months

http://www.sqlhacks.com/index.php/Dates/GroupByMonth

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
----------- ----------- ---------------------------------------
2004        1           0.00
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?


I am using MySQL

[Dan Grossman]

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.

[chumba]

good point ! I could have solved this already if I had just done it in the php code, rather than all the time I wasted playing around with SQL !

[chumba]

but as a matter of interest, how would one do it using SQL?

[disgracian]

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.

Cheers,
D.

[chumba]

can you point to any link that shows how this is done, or provide the code yourself.

I can't find a single example of how to do this but will check my "simply sql" book when i get home, it might be in there.

[r937]

... it might be in there.

it isn't

i recommend that you use a numbers table --

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

depending on how you want to use the data

make sense?

[chumba]

OK, i'm with you so far... how about the last step of joining the tables together to output the data?

TIA

[r937]

what does your table look like? is there a date field?

[chumba]

Hi rudy, I'm using the example data from
http://www.sqlhacks.com/index.php/Dates/GroupByMonth

[r937]

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

[chumba]

thanks rudy, i'll give it a try and see what happens !

Then i can hopefully modify it to fit my own application.

[yodlan]

someone has this problem solved?

[r937]

someone has this problem solved?

yeah, me

[chumba]

It's amazing how many forum threads there are with people asking about this kind of query. None of them had any proper solution.

Rudy. maybe some good content for "simply sql 2"

[SpacePhoenix]

It's amazing how many forum threads there are with people asking about this kind of query. None of them had any proper solution.

Rudy. maybe some good content for "simply sql 2"

Or a SQL reference book (details of SQL functions, which database servers support them, etc).