you need a Calendar table of some sort which gives you the arraay of all possible months;from that you change your query to select from that table, and join to your users table.then you get sum() with zeros you are looking for:

select COUNT(*) AS NewUsers,AllPossibleMonthsAndYears.Month AS MNTH,AllPossibleMonthsAndYears.Year AS YR

I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[0-3][0-9]-00'

That looks like cool local dialect for MySql. How can we port that to ANSI sql?

Lowell (1/4/2013)you need a Calendar table of some sort which gives you the arraay of all possible months;from that you change your query to select from that table, and join to your users table.then you get sum() with zeros you are looking for:

select COUNT(*) AS NewUsers,AllPossibleMonthsAndYears.Month AS MNTH,AllPossibleMonthsAndYears.Year AS YR

I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[0-3][0-9]-00'

That looks like cool local dialect for MySql. How can we port that to ANSI sql?

And something else I don't get: as much as Joe posts responses here, you would think he actually cared about helping people. But I know we've all seen this *exact* same response of his umpteen times. If he cared he would not be spamming us. But he *is* spamming us. So he *must* not care. So why is he here again?

Lowell (1/4/2013)you need a Calendar table of some sort which gives you the arraay of all possible months;from that you change your query to select from that table, and join to your users table.then you get sum() with zeros you are looking for:

select COUNT(*) AS NewUsers,AllPossibleMonthsAndYears.Month AS MNTH,AllPossibleMonthsAndYears.Year AS YR