CODEWITHSUNDEEP
mysqlsql
Fuxi
Fuxi

Reputation: 7589

mySQL query with count / grouping

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.

Upvotes: 0

Views: 38

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

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

Upvotes: 2

Related Questions