Count different distincts in one query 1

[theScien]

Hi guys, any way to count different distincts in one query?

Sub-queries not supported in my MySQL v4.0.??

I have this:

'// Count unique months and years to dimension arrays accordingly
SQL = "SELECT COUNT(DISTINCT Month(Issue)) AS mm FROM tbl_Newsletters WHERE Active = 1;"
Set rsNL = Conn.Execute(SQL)
ReDim arrMonth(rsNL("mm"))

SQL = "SELECT COUNT(DISTINCT Year(Issue)) AS yy FROM tbl_Newsletters WHERE Active = 1;"
Set rsNL = Conn.Execute(SQL)
ReDim arrYear(rsNL("yy"))

As you can see I'm querying twice, works fine, however I would prefer a way to do it in one query.

Anyone?

 

[TonyGroves]

How about:
[tt]
SELECT
COUNT(DISTINCT MONTH(issue)) mm,
COUNT(DISTINCT YEAR(issue)) yy
...
[/tt]

 

[theScien]

Great stuff, never tried this as it didn't appear to be correct syntax somehow.

Thanks for that.

 

[towerbase]

That's worth a star, Tony. I didn't know you could do that either.

Andrew
Hampshire, UK

 

[TonyGroves]

Thanks Andrew!