CODEWITHSUNDEEP
mysqlcount
Silviu G
Silviu G

Reputation: 1301

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

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?

Upvotes: 1

Views: 36

Answers (1)

Kickstart
Kickstart

Reputation: 21513

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

Upvotes: 1

Related Questions