Is it possible to select a distinct column and get another column’s value count? [closed]

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?

Answer

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;