Count the output rows of an sql query?

Question

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.

Have you tried count(distinct list_type.type)

Jefferson
– Jefferson

2013-01-28 16:00:53 +00:00
Commented Jan 28, 2013 at 16:00 — Jefferson
– Jefferson, Commented Jan 28, 2013 at 16:00

fthiella · Accepted Answer · 2013-01-28 16:09:54Z

1

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:

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`)

answered Jan 28, 2013 at 16:09

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1658688 Over a year ago

Thanks that worked. I did this in the past but did not add the "s" after the ) so it did not work.

fthiella Over a year ago

@user1658688 you're welcome! MySql requires that you give an alias to all subqueryes, I used just s that stands for subquery, but you can use something more descriptive like groupedEvents or anything you like

fthiella Over a year ago

@user1658688 pls don't forget to accept the answer if you find it useful :) tnx

John Woo · Accepted Answer · 2013-01-28 16:01:12Z

0

GROUPed then by 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

answered Jan 28, 2013 at 16:01

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

Naftali · Accepted Answer · 2013-01-28 16:01:18Z

0

Don't group by events.name

Try grouping by list_type.type

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

answered Jan 28, 2013 at 16:01

Naftali

147k41 gold badges247 silver badges304 bronze badges

Collectives™ on Stack Overflow

Count the output rows of an sql query?

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related