count value in three separate columns - Rails/SQL

Question

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;

I suspect it's how I'm grouping.

Is this an existing database or are you working on the design. It doesn't look good :). Repetitive columns (such as country, country2, country3) should be in a separate table (roast_id and country). Moreover, countries should also be in another table (referenced by their country_id) and not just country names. This would allow a more flexible solution (is 3 countries an absolute maximum?), less errors in country names, and an easier query (select countries.name, count(*) from roasts, country_roasts, countries where roast_id = roasts.id and country_id = countries.id group by countries.name) — Pablo
– Pablo, Commented Feb 1, 2018 at 13:22
@Pablo. It's an existing database that I'm adding more complexity to. It's actually worse than I describe here, because each country would have a number of regions as well, and actually, each coffee would have associated attributes such as altitude, process and variety However I don't know what all these values are, and am leaving it to the user to create these. — Simon Cooper
– Simon Cooper, Commented Feb 1, 2018 at 13:33

Gordon Linoff · Accepted Answer · 2018-02-01 13:05:37Z

1

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).

answered Feb 1, 2018 at 13:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

2 Comments

Vishal Over a year ago

Can you help me to figure out my issue ? here is my question stackoverflow.com/questions/48563576/…

Simon Cooper Over a year ago

This does indeed answer my question. Although I now have much bigger questions/decisions on @pablo comment!

Collectives™ on Stack Overflow

count value in three separate columns - Rails/SQL

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related