按月和年分组，从另一个表中计数

我试图让我的查询按月份和年份对行进行分组assignments表，并统计具有特定值的行数leads桌子。它们作为assignments表有一个id_lead场，即id中的行的leads table.

d_new将是网站为 newsite.com 的月份潜在客户分配的计数d_subprime将是网站不是 newsite.com 的月份潜在客户分配的计数

这是正在使用的表：

`leads`
id (int)
website (varchar)

`assignments`
id_lead (int)
date_assigned (int)

这是我的查询不起作用：

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

$id_dealership是一个变量，其中包含我试图查看其计数的经销商的 ID。

任何帮助将不胜感激。

您可以将时间戳截断为几个月，并使用获得的值进行分组，然后从中导出必要的日期部分：

SELECT
  YEAR(d_yearmonth) AS d_year,
  MONTHNAME(d_yearmonth) AS d_month,
  …
FROM (
  SELECT
    LAST_DAY(FROM_UNIXTIME(a.date_assigned)) as d_yearmonth,
    …
  FROM assignments AS a
    LEFT JOIN leads AS l ON (l.id = a.id_lead)
  WHERE id_dealership = '$id_dealership2'
  GROUP BY
    d_yearmonth
) AS s
ORDER BY
  d_year            ASC,
  MONTH(d_yearmonth) ASC

Well, LAST_DAY()并没有真正截断时间戳，但它确实将属于同一月份的所有值转换为相同的值，这基本上就是我们所需要的。

我想计数应该与您实际选择的行相关，这不是您的子查询。像这样的事情可能会做：

…
COUNT(d.website = 'newsite.com' OR NULL) AS d_new,
/* or: COUNT(d.website) - COUNT(NULLIF(d.website, 'newsite.com')) AS d_new */
COUNT(NULLIF(d.website, 'newsite.com'))  AS d_subprime
…

这是包含所有提到的修改的整个查询：

SELECT
  YEAR(d_yearmonth) AS d_year,
  MONTHNAME(d_yearmonth) AS d_month,
  d_new,
  d_subprime
FROM (
  SELECT
    LAST_DAY(FROM_UNIXTIME(a.date_assigned)) as d_yearmonth,
    COUNT(d.website = 'newsite.com' OR NULL) AS d_new,
    COUNT(NULLIF(d.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_yearmonth
) AS s
ORDER BY
  d_year            ASC,
  MONTH(d_yearmonth) ASC