grupo por mes y año, cuente desde otra tabla
estoy tratando de obtener mi consulta para agrupar filas por mes y año desde elassignments, y cuente el número de filas que tiene un cierto valor deleads mesa. están unidos entre sí comoassignments table tiene unid_lead campo, que es elid de la fila en elleads mesa
d_new would be a count of the assignments for leads for the month whose website is newsite.com <code>d_subprime</code> would be a count of the assignments for leads for the month whose website is not newsite.com
<p>here are the tables being used:</p><pre><code>`leads`
id (int)
website (varchar)
`assignments`
id_lead (int)
date_assigned (int)
</code></pre><p>heres my query which is not working:</p><pre><code>SELECT
MONTHNAME(FROM_UNIXTIME(a.date_assigned)) as d_month,
YEAR(FROM_UNIXTIME(a.date_assigned)) as d_year,
(select COUNT(*) from leads where website='newsite.com' ) as d_new,
(select COUNT(*) from leads where website!='newsite.com') as d_subprime
FROM assignments as a
left join leads as l on (l.id = a.id_lead)
where id_dealership='$id_dealership2'
GROUP BY
d_month,
d_year
ORDER BY
d_year asc,
MONTH(FROM_UNIXTIME(a.date_assigned)) asc
</code></pre><p><code>$id_dealership</code> is a variable containing a id of the dealership im trying to view the count for.</p>any help would be greatly appreciated.