CODEWITHSUNDEEP
sqlsqlitesubquery
c3688014
c3688014

Reputation: 47

Finding most common elements in year in SQL

I have a table that has three attributes, year, breed and color. For example:

╔════╤═════════╤═══════╤══════╗
║ id │ breed   │ color │ year ║
╠════╪═════════╪═══════╪══════╣
║ 01 │ pug     │ black │ 2019 ║
╟────┼─────────┼───────┼──────╢
║ 02 │ pug     │ black │ 2019 ║ 
╟────┼─────────┼───────┼──────╢
║ 03 │ poodle  │ brown │ 2019 ║
╟────┼─────────┼───────┼──────╢
║ 04 │ pug     │ white │ 2013 ║
╟────┼─────────┼───────┼──────╢
║ 05 │ poodle  │ brown │ 2013 ║ 
╟────┼─────────┼───────┼──────╢
║ 06 │ poodle  │ white │ 2010 ║  
╟────┼─────────┼───────┼──────╢
║ 07 │ bulldog │ white │ 2010 ║
╟────┼─────────┼───────┼──────╢
║ 08 │ husky   │ brown │ 2012 ║
╟────┼─────────┼───────┼──────╢
║ 09 │ pug     │ black │ 2013 ║
╟────┼─────────┼───────┼──────╢
║ 10 │ husky   │ brown │ 2014 ║
╚════╧═════════╧═══════╧══════╝

Creating the table

create table dogs (
 id     char(5),
 breed      char(10),
 year       int,
 color      char(10),
 primary key (id)
 );

For each year of a dog, I need to find the most frequent breed and the most frequent dog color adopted, and if there are ties, list all the ties. I have tried the following: 

SELECT d.year, d.breed,COUNT(d.breed),d.color,COUNT(v.color)
FROM dogs d
GROUP BY d.year,d.breed,d.color;

This essentially just gets me every year's different breeds and how many of each color there is. How would I do the above question? Also I am using SQLite.

Upvotes: 1

Views: 103

Answers (2)

peak
peak

Reputation: 116919

The following shows how to compute, for each year, the most frequent breeds, without the need for RANK.

It might be easiest to formulate a separate (identically structured) query for determining the most frequent colors in each year.

with frequencies as (select year, breed, count(*) as breedcount from dogs GROUP BY breed, year),
     maxes       as (select year, max(breedcount) mx from frequencies GROUP BY year)
select frequencies.year year, breed, mx
     from frequencies JOIN maxes ON frequencies.year = maxes.year
     where breedcount = mx ORDER BY year ;

Output (with headers)

year|breed|mx
2010|bulldog|1
2010|poodle|1
2012|husky|1
2013|pug|2
2014|husky|1
2019|pug|2

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522406

We can try using RANK if your version of SQLite is 3.25.0 or later:

WITH cte AS (
    SELECT d.year, d.breed, d.color, COUNT(d.breed) AS cnt,
        RANK() OVER (ORDER BY COUNT(d.breed) DESC) rnk
    FROM dogs d
    GROUP BY d.year, d.breed, d.color
)

SELECT year, breed, color, cnt
FROM cte
WHERE rnk = 1;

If your version of SQLite does not support window functions, and you expect to have reporting requirements similar to this moving forward, then consider upgrading.

Upvotes: 1

Related Questions