按月和按年，从另一个表 [英] group by month and year, count from another table

问题描述

我试图从赋值表中获取我的查询以按月份和年份对行进行查询，并计算从<$ c中有一定值的行数$ c> leads 表。它们被链接在一起，因为赋值表具有 id_lead 字段，其是 / code>表中 c $

im trying to get my query to group rows by month and year from the assignments table, and count the number of rows that has a certain value from the leads table. they are linked together as the assignments table has an id_lead field, which is the id of the row in the leads table.

/ code>将是网站为newsite.com的月份潜在客户分配的计数。
d_subprime 将是指定作业的计数

这里是正在使用的表格：

here are the tables being used:

`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

b $ b

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

$id_dealership is a variable containing a id of the dealership im trying to view the count for.

任何帮助将非常感激。

推荐答案

您可以将时间戳截短到几个月，分组，然后从中导出必要的日期部分：

You can sort of truncate your timestamps to months and use the obtained values for grouping, then derive the necessary date parts from them:

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

好吧， LAST_DAY（）不会真正截断时间戳，但会将属于同一月份的所有值转换为相同的值，这基本上是我们需要。

Well, LAST_DAY() doesn't really truncate a timestamp, but it does turn all the values belonging to the same month into the same value, which is basically what we need.

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

And I guess the counts should be related to the rows you are actually selecting, which is not what your subqueries are. Something like this might do:

…
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
…

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

Here's the entire query with all the modifications mentioned:

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

这篇关于按月和按年，从另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！