Sub query / group by problems

Databases
1

Hi

Im having a few SQL problems and wondering if anybody can help me out a bit?

Here is what I have so far in my SQL:

SELECT Year(registerDate)  AS yearval, Month(registerDate)  AS monthval, Count(id) AS countvalall
FROM t_users
WHERE Year(registerDate) > 2009
GROUP BY Year(registerDate), Month(registerDate)
ORDER BY Year(registerDate), Month(registerDate)

This works absolutely fine but I want to have another column to count the number of users that have actually activated their account, its something like: where activation <> ‘’ .

So I’ve been trying to get the following to work with a sub query:

SELECT Year(registerDate)  AS yearval, Month(registerDate)  AS monthval,  Count(id) AS countvalall, (SELECT Count(id) AS countvalact WHERE  activation <> '')
FROM t_users
WHERE Year(registerDate) > 2009
GROUP BY Year(registerDate), Month(registerDate)
ORDER BY Year(registerDate), Month(registerDate)

Obviously I’m missing something as this doesnt work. I would be grateful for any advice on what I need to change to get this to work.

Many thanks

PS. MySQL version is 3.23.54 (I know its old)

2

Thanks swampBoogie, that worked like a charm! Much appreciated.

3 

select Year(registerDate)  AS yearval, 
       Month(registerDate)  AS monthval, 
       Count(id) AS countvalall,
       sum(case when activation <> '' then 1 else 0 end) as countvalactivated
  from t_users
 where Year(registerDate) > 2009
 group by Year(registerDate), Month(registerDate)
 order by Year(registerDate), Month(registerDate)