Is it possible to select a distinct column and get another column's value count?

Question

I think I might know enough to do parts individually but is it possible to do it with one statement? I need to display a model count for each year it appears in.

I have the following data:

id  model    year
-----------------
1   45A      1992
2   45A      1992
3   45B      1992
4   45A      1996
5   45B      1996
6   33C      2000
7   33C      2000
8   45B      2000
9   45B      2010

It should come out something like:

year  model  count
------------------
1992  45A    2
1992  45B    1
1996  45A    1
1996  45B    1
2000  33C    2
2000  45B    1
2010  45B    1

How do I accomplish this in SQL? Is it a group by year and count the models?

hi, seems group by with 2 columns yes. possible duplicate of https://stackoverflow.com/q/2421388/1827276 — boly38, May 15 '19 at 21:01
Possible duplicate of [Using group by on multiple columns](https://stackoverflow.com/questions/2421388/using-group-by-on-multiple-columns) — The Impaler, May 15 '19 at 21:13

score 3 · Accepted Answer · answered May 15 '19 at 21:07

Unless I'm overlooking something, you just need to GROUP BY the two columns you're interested in. I changed the name of the last column to avoid any keyword issues.

SELECT
  year,
  model,
  count(*) as modelcount
FROM
  table
GROUP BY
  year,
  model
ORDER BY 
  year;

Is it possible to select a distinct column and get another column's value count?

1 Answers1