计算sql查询的输出行？

SELECT 
    `list_type`.`type`
FROM
    `events`
        INNER JOIN
    `list_type` ON `events`.`type` = `list_type`.`id`
WHERE
    `events`.`date` BETWEEN '2012-01-01' AND '2012-12-31'
        AND `events`.`center_id` = 2
GROUP BY `events`.`name`

Baseline
Baseline
Baseline
Baseline
Baseline
Baseline
Baseline
Special Events
Special Events
Special Events

But I would like it to count each type like this

但我希望它能像这样计算每种类型

Baseline    7
Special Events  3

so i tried this

所以我试过这个

SELECT 
    `list_type`.`type`,
    COUNT(*)
FROM
    `events`
        INNER JOIN
    `list_type` ON `events`.`type` = `list_type`.`id`
WHERE
    `events`.`date` BETWEEN '2012-01-01' AND '2012-12-31'
        AND `events`.`center_id` = 2
GROUP BY `events`.`name`

But it gives me this:

但它给了我这个:

Baseline    45
Baseline    3
Baseline    56
Baseline    23
Baseline    12
Baseline    9
Baseline    2
Special Events  5
Special Events  4
Special Events  18

It is counting the number of each name and type not the output form query. I also tried COUNT(list_type.type) Thanks for your help.

它计算每个名称的数量和类型而不是输出表单查询。我也尝试过COUNT(list_type.type)感谢您的帮助。

3 个解决方案

#1

GROUPed then by list_type.type

然后由list_type.type进行分组

SELECT  list_type.type,
        COUNT(*) totalCount
FROM    events
        INNER JOIN list_type 
            ON events.type = list_type.id
WHERE   events.date BETWEEN '2012-01-01' AND '2012-12-31'
        AND events.center_id = 2
GROUP   BY list_type.type

#2

Don't group by events.name

不要按events.name分组

Try grouping by list_type.type

尝试按list_type.type分组

Since you were grouping by event, then it was giving you results and numbers based on events!

由于您按事件分组,因此它根据事件为您提供结果和数字!

#3

You can always count the result of your query using a subquery, like this:

您始终可以使用子查询来计算查询结果,如下所示:

SELECT type, count(*)
FROM (
  ...your query above...
) s
GROUP BY type

it will surely work, but it's not necessarily the best way to proceed. I think you should just group by list_type.type:

它肯定会起作用,但它不一定是最好的方法。我认为你应该按list_type.type进行分组:

GROUP BY list_type.type

and then you have to count distinct events name for every type, like this:

然后你必须为每种类型计算不同的事件名称,如下所示:

SELECT 
    `list_type`.`type`,
    COUNT(DISTINCT `events`.`name`)

#1