Mysql: Counting occurences in a table, return as a single row

Question

I have a table listing some organizations, and some details, as the country they are in.

I need to count the number of organizations in each country, and I managed that with this query:

SELECT COUNTRY, COUNT(*) AS ORGANIZATIONS FROM ORGANIZATION GROUP BY COUNTRY

This gives me a result set like this:

COUNTRY | ORGANIZATION
--------+-------------
UK      | 12
FR      | 7
NL      | 9

This is good, but a better result I'm looking for is a single row result, like this:

UK | FR | NL
---+----+---
12 | 7  | 9

Any ideas on how to do that in MySQL alone?

possible duplicate of [MySQL pivot table](http://stackoverflow.com/questions/7674786/mysql-pivot-table) — RandomSeed, Jul 24 '14 at 08:42

score 1 · Accepted Answer · answered Jul 24 '14 at 09:59

This can't really be done with a single SQL statement as it would require an unknown number of columns.

I would be inclined to say that this is just formatting of the returned data, and hence probably better places in the calling script.

However it is easy to do if you have a fixed number of countries that you are interested in. But with several sub queries it is not likely to be that efficient.

What can be done is to dynamically build up the SQL, but that is messy.

Alternative would to GROUP_CONCAT the countries details into a single row:-

SELECT GROUP_CONCAT(CONCAT_WS('##', COUNTRY, ORGANIZATIONS ))
FROM
(
    SELECT COUNTRY, COUNT(*) AS ORGANIZATIONS 
    FROM ORGANIZATION 
    GROUP BY COUNTRY
) sub0

Indeed, it cannot be done with a single query (apparently it's so by design), and like you said, this formatting belongs more likely to the presentation layer, so in the end (after considering the database processing overhead) I have used my initial query and simply looped through all the values there. Thanks for making the situation clear! — Silviu G, Jul 24 '14 at 10:25

Mysql: Counting occurences in a table, return as a single row

1 Answers1