Skip to content
Advertisement

MySQL count values and put custom columns

I have this query:

SELECT count(password) as normal, count(fb_id) as fb, count(google_id) as google, count(linkedin_id) as linkedin FROM `users` 

which returns (correctly) :

normal | fb | google | linkedin     
-------------------------------
2      |  2 |   1    | 1

Now what if I want to group them in custom columns? Like:

type     | count
------------
fb       | 2
normal   | 2
google   | 1
linkedin | 1

I do not have such columns in my table, so I have to get them somehow in the query. Is it even possible?

Advertisement

Answer

You need UNION of four queries. Do a try:

SELECT 'normal' as `type`, count(password) as `count` FROM `users`
UNION
SELECT 'fb' as `type`, count(fb_id) as `count` FROM `users`
UNION
SELECT 'google' as `type`, count(google_id) as `count` FROM `users`
UNION
SELECT 'linkedin' as `type`, count(linkedin_id) as `count` FROM `users`

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement