I have a db with one column indicating the name of a person, one column the task and a third with the year of that task:
| person | task | year |
|---|---|---|
| Bill | task 1 | 2017 |
| Bill | task 2 | 2017 |
| Bill | task 2 | 2018 |
| Hanna | task 2 | 2019 |
| Hanna | task 0 | 2016 |
| … |
Now I want to use sqlite to get it to show the total number of tasks per year per person and order it by year, so:
| person | year | tasks |
|---|---|---|
| Hanna | 2016 | 1 |
| Bill | 2017 | 2 |
| Bill | 2018 | 1 |
| Hanna | 2019 | 1 |
My request:
SELECT author, year, count(year) as tasks
FROM db
GROUP BY author, year
ORDER BY author, year ASC;
But this creates an order by author, not by year.
Flipping author and year in the ORDER BY section doesn’t work.
>Solution :
If this "does not work"
SELECT author, year, count(year) as tasks
FROM db
GROUP BY author, year
ORDER BY year ASC, author;
try this
SELECT author, year, tasks
FROM (
SELECT author, year, count(year) as tasks
FROM db
GROUP BY author, year
) X
ORDER BY year ASC, author;