MySQL: Count certain values

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:

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

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

---

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.

---

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.