count and group by

[scarhand]

I'll show you what I'm trying to do in the following query, with this next query you can see I'm trying to get the c_year variable to be a count of the rows with the website for the current year, however the number is showing the same for all results:

 

$sql = mysql_query("select `website`, (select count(*) from `assignments` where YEAR(FROM_UNIXTIME(`date_assigned`)) = YEAR(CURDATE())) as c_year, count(*) as c_all
                    from `assignments`
                    group by `website`
                    order by `website` asc
                    ");

 

So it is showing "4433" in every row of the results for each website...

 

However the "c_all" variable shows a perfect count.

[JustLikeIcarus]

Try something like this.

 

select `website`, sum(case when YEAR(FROM_UNIXTIME(`date_assigned`)) = YEAR(CURDATE()) then 1 else 0 end) AS c_year, count(*) as c_all
from `assignments`
group by `website`
order by `website` asc