Counting the results of different rows in a MySQL table

Question

I am trying to count the entries in rows in a MySQL table:

Example data:

+------------------------------------------------+
| facebook_id | linkedin_id | twitter_id | vk_id |
+------------------------------------------------+
  1234143134    0             13413993     13499
  0             0             0            0
  12313431334   0             13413243     0
  12341431340   13419194319   13419943     13413
  0             0             0            0
+------------------------------------------------+

The aim is to display how many of each _id has an entry. In the above example, facebook has 3, LinkedIn has 1 and twitter has 3 and vk has 2.

Here is my attempt at counting them:

COUNT(`facebook_id`) AS `facebook`,
COUNT(`linkedin_id`) AS `linkedin`,
COUNT(`twitter_id`) AS `twitter`,
COUNT(`vk_id`) AS `vkontakte`
FROM `user`

How can this be fixed? Thanks :)

forpas · Accepted Answer · 2019-10-21 18:31:45Z

2

Use SUM() like this:

select 
  sum(facebook_id <> 0) facebook, 
  sum(linkedin_id <> 0) linkedin,
  sum(twitter_id <> 0) twitter,
  sum(vk_id <> 0) vk
from `user`

Each of the boolean expressions like:

facebook_id <> 0

evaluates to 1 or 0.
See the demo.
Results:

| facebook | linkedin | twitter | vk  |
| -------- | -------- | ------- | --- |
| 3        | 1        | 3       | 2   |

edited Oct 21, 2019 at 18:31

answered Oct 21, 2019 at 18:26

forpas

165k10 gold badges51 silver badges85 bronze badges

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

2 Comments

spencer7593 Over a year ago

we could also write SUM(CASE WHEN facebook_id <> 0 THEN 1 ELSE 0 END) to get an equivalent result +10

forpas Over a year ago

Your code is the standard way for this requirement, but since MySql offers this feature I use it.

Collectives™ on Stack Overflow

Counting the results of different rows in a MySQL table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related