MySQL: Count certain values

Question

SELECT reporting.station FROM reporting LEFT JOIN channel ON MD5(igmpurl)=reporting.station WHERE reporting.station!="n/a" ORDER BY name;

resulting in this table:

Query like it is

Now I'd like to count the number of each element, which should something look like:

Result like I'd would like to have it

Note: I know about COUNT(station) which would return the number of the rows (20) but is not what I want.

Any idea how to solve this in MySQL (InnoDB)? Many thanks and regards

Headshota · Accepted Answer · 2011-04-05 07:09:03Z

3

SELECT
    reporting.station, COUNT(reporting.station)
FROM
    reporting
LEFT JOIN
    channel
ON
    MD5(igmpurl)=reporting.station
WHERE
    reporting.station!="n/a"
GROUP BY reporting.station
ORDER BY
    name;

Try this.

answered Apr 5, 2011 at 7:09

Headshota

21.4k13 gold badges63 silver badges82 bronze badges

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

Comments

Piskvor left the building · Accepted Answer · 2011-04-05 07:33:48Z

1

Use COUNT() and GROUP BY:

In your case, it should be something like this:

SELECT
    reporting.station,
    COUNT(*) AS rep_station_count
FROM
    reporting
LEFT JOIN
    channel
ON
    MD5(igmpurl)=reporting.station
WHERE
    reporting.station!="n/a"
GROUP BY
    reporting.station
ORDER BY
    name;

Note that allowing normal and aggregate columns (e.g. AVG(),COUNT() etc.) in the same query is MySQL specific.

edited Apr 5, 2011 at 7:33

answered Apr 5, 2011 at 7:10

Piskvor left the building

93.1k46 gold badges182 silver badges226 bronze badges

3 Comments

Piskvor left the building Over a year ago

(also, I'm not quite sure what you need the ORDER BY for, but that's not directly related to your question)

Atmocreations Over a year ago

Thanks to you too. I'll give Headshota the accept because he was 2 minutes faster :P (and he needs the reputation more than you do :P) ORDER BY isn't that important. May I somehow get the row with the highest count? Or is the easiest way to ORDER BY the count and take the first?

Piskvor left the building Over a year ago

@Atmocreations: For getting the highest count, you're on the right track: in this case, it would be ORDER BY rep_station_count DESC - first row is the one you want. Easiest and probably fastest, too :)

Collectives™ on Stack Overflow

MySQL: Count certain values

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related