Count specific values of a column in SQL and displ

问题:

I have to following table named results:

Position | Country
-------------------
1        | US
2        | Italy
3        | France
1        | US
2        | France
3        | Italy

and I want to create the following

Country | 1 | 2 | 3 |
---------------------
US      | 2 | 0 | 0 |
Italy   | 0 | 1 | 1 |
France  | 0 | 1 | 1 |

I believe the best way to move forward is using pivot tables, can someone give me advice on how to proceed?

回答1:

You can use conditional aggregation:

select country,
       sum(case when position = 1 then 1 else 0 end) as pos_1,
       sum(case when position = 2 then 1 else 0 end) as pos_2,
       sum(case when position = 3 then 1 else 0 end) as pos_3
from t
group by country
order by sum(pos) asc;