CODEWITHSUNDEEP
sqlruby-on-railspostgresql
Simon Cooper
Simon Cooper

Reputation: 1594

count value in three separate columns - Rails/SQL

Have a table roasts that has three columns country, country2, country3. The columns are for indicating which countries a blend of coffee comes from. It, therefore, goes that a country name could appear in any one of the three columns. Whilst I can do a count on any given column, I'd like to count how many times a value appears in all three of the country columns.

I'd like to do this in Rails/ActiveRecord. I've got as far as the below SQL, but this output isnt right: 

SELECT country, country2, country3, COUNT(*) AS CountryCount FROM roasts GROUP BY country, country2, country3;

enter image description here

I suspect it's how I'm grouping.

Upvotes: 1

Views: 120

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

You should have a table called something like CoffeeCountries that has one row per country for each blend. I would strongly recommend that you change the data structure.

With your data structure, you need to unpivot the data. Because your data is small and you are probably not familiar with lateral joins, I'm going to use the union all approach:

select country, count(*)
from ((select country1 as country from roasts) union all
      (select country2 as country from roasts) union all
      (select country3 as country from roasts)
     ) c
where country is not null
group by country
order by count(*) desc;

If you had a large table, I would recommend the lateral join. This version scans the table three times (once for each country).

Upvotes: 1

Related Questions