CODEWITHSUNDEEP
phpmysqlsql
NewJsGuy
NewJsGuy

Reputation: 113

Get number of currency on mysql

I have 3 tables, 

Table Country
---------------------
id_country |country |
---------------------
      1     Brazil
      2     Chile
      3     Colombia

Table Users
------------------------
id_country |colorNumber|
------------------------
      1          1
      1          2
      1          2
      1          3
      2          2
      2          4
      3          1
      3          4

Table Colors
---------------------
id_color | colorName|
---------------------
     1        Red
     2        Blue
     3        Fuschia
     4        Black

And I want to know what was the color most picked in a country

I made something like:

select color ,count(color), country as country from users group by (country) order by(color) desc

But the results is wrong, is not the expected one, what I'm doing wrong ?

Thanks in advance and sorry if the question is a little noob.

Upvotes: 0

Views: 58

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270421

The following query gets you the count of colors for each country:

select cc.country, c.colorName, count(*) as cnt
from users join
     colors
     on users.colorNmber = colors.id_color join
     countries cc
     on users.id_country = cc.id_country
group by cc.country, c.colorName;

You want the most popular one. Here is a trick to do this in MySQL:

select country,
       substring_index(group_concat(colorName order by cnt desc), ',', 1) as MostPopularColor
from (select cc.country, c.colorName, count(*) as cnt
      from users join
           colors
           on users.colorNmber = colors.id_color join
           countries cc
           on users.id_country = cc.id_country
      group by cc.country, c.colorName
     ) t
group by country;

Upvotes: 1

Related Questions