I have a table of levels and years that looks roughly like this:
INSERT INTO demo(first_name, rank, year) AS VALUES
('Peter',2,2017), ('Robert',3,2016), ('Evelyn',2,2017),
('Rand',3,2017), ('Kofi',3,2017), ('Daniel',3,2016), ('Leonard',1,2017),
('Mary',1,2017), ('Frank',1,2017), ('John',3,2017), ('Mary',1,2016),
('Gilbert',3,2016), ('Quince',2,2017), ('David',3,2017), ('Louis',2,2017),
('Felipe',2,2016), ('Alfonso',1,2017), ('Elise',3,2016), ('Jorge',3,2017),
('Mary',3,2016), ('Jasvant',2,2017), ('Paul',3,2017), ('Richard',2,2017),
('Christopher',2,2017), ('Johannes',2,2016), ('Nasser',3,2016), ('Bruce',3,2017),
('John',3,2016), ('Frederic',3,2016);
I can capture totals for each level and year with a pretty basic COUNT()
:
SELECT rank, COUNT(*), year
FROM demo
GROUP BY rank, year
ORDER BY year, rank;
That gives me a fairly tidy summary of the demo data:
Rank Count Year
1 1 2016
2 2 2016
3 8 2016
1 4 2017
2 7 2017
3 7 2017
My actual data is a lot bigger, and I'd like to be able to see it in a summary table that looks something more like this:
Rank 2016 2017
1 1 4
2 2 7
3 8 7
Is there a straightforward way to query a pivot-like view like this? For a much wider range of ranks and years?