mySQL query with count / grouping

Question

I'm having the following data:

ID   user    value     lang
------------------------------
1    1       foo1      de
2    1       foo2      de
3    1       bar1      en
4    1       bar2      en
5    1       bar3      en
6    1       bar4      fr

desired output:

user   de    en    fr 
------------------------------
1      2     3     1

i want to get the total count of all languages from user 1.

I've tried with COUNT, SUM and HAVING .. but it didn't work.

Can you please add what you have tried? Did you group by?

user3783243
– user3783243

2020-01-30 13:43:59 +00:00
Commented Jan 30, 2020 at 13:43 — user3783243
– user3783243, Commented Jan 30, 2020 at 13:43

Barbaros Özhan · Accepted Answer · 2020-01-30 13:56:45Z

2

You can use conditional aggregation

SELECT user,
       SUM(CASE WHEN lang='de' THEN 1 ELSE 0 END) as 'de',
       SUM(CASE WHEN lang='en' THEN 1 ELSE 0 END) as 'en',
       SUM(CASE WHEN lang='fr' THEN 1 ELSE 0 END) as 'fr'
  FROM t
 GROUP BY user

or directly without conditionals :

SELECT user,
       SUM(lang='de') as 'de',
       SUM(lang='en') as 'en',
       SUM(lang='fr') as 'fr'
  FROM t
 GROUP BY user

Demo

edited Jan 30, 2020 at 13:56

answered Jan 30, 2020 at 13:48

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Collectives™ on Stack Overflow

mySQL query with count / grouping

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related