英文:
One SQL statement for multiple column totals based on year
问题
SELECT
COUNT(DISTINCT CASE WHEN Year = 2020 THEN Country END) AS "2020",
COUNT(DISTINCT CASE WHEN Year = 2021 THEN Country END) AS "2021",
COUNT(DISTINCT Country) AS Total_Unique_Countries
FROM MYTABLE
英文:
I'm trying to return the total number of unique countries listed for each year, and the total number of unique countries in the entire table. The table is formatted like this:
Country | Year | State | V1 | V2 |
US 2020 NY 9 2
US 2020 MA 3 6
CA 2020 MAN 2 8
CA 2020 ONT 5 1
AU 2020 TAS 7 2
AU 2020 VIC 3 3
US 2021 NY 2 0
US 2021 MA 8 2
AU 2021 TAS 4 1
AU 2021 VIC 5 2
I want my query to return this:
2020 | 2021 | Total_Unique_Countries
3 2 3
I tried:
SELECT
SUM(CASE WHEN YEAR=2020 THEN 1 ELSE 0 END) AS "2020",
SUM(CASE WHEN YEAR=2021 THEN 1 ELSE 0 END) AS "2021",
COUNT(DISTINCT COUNTRY) AS Total_Unique_Countries
FROM MYTABLE GROUP BY YEAR
The result:
2020 | 2021 | Total_Unique_Countries
6 0 3
0 4 2
答案1
得分: 0
在一个公用表达式（CTE）中，您可以首先消除重复项，然后进行计数。
WITH CTE as (SELECT DISTINCT "Country", "Year" FROM MYTABLE)
SELECT
SUM(CASE WHEN "Year"=2020 THEN 1 ELSE 0 END) AS "2020",
SUM(CASE WHEN "Year"=2021 THEN 1 ELSE 0 END) AS "2021",
COUNT(DISTINCT "Country") AS Total_Unique_Countries
FROM CTE
| 2020 | 2021 | total_unique_countries |
|---|---|---|
| 3 | 2 | 3 |
这是代码部分的翻译，您可以在执行该代码时得到相应的结果。
<details>
<summary>英文:</summary>
You can first elimnate the duplicates in a CTE and then count
WITH CTE as (SELECT
DISTINCT "Country", "Year" FROM MYTABLE)
SELECT
SUM(CASE WHEN "Year"=2020 THEN 1 ELSE 0 END) AS "2020",
SUM(CASE WHEN "Year"=2021 THEN 1 ELSE 0 END) AS "2021",
COUNT(DISTINCT "Country") AS Total_Unique_Countries
FROM CTE
| 2020 | 2021 | total\_unique\_countries |
|-----:|-----:|-----------------------:|
| 3 | 2 | 3 |
> ``` status
> SELECT 1
> ```
[fiddle](https://dbfiddle.uk/1QrVzLYE)
</details>
# 答案2
**得分**: 0
SELECT
COUNT(DISTINCT CASE WHEN YEAR=2020 THEN COUNTRY END) AS "2020",
COUNT(DISTINCT CASE WHEN YEAR=2021 THEN COUNTRY END) AS "2021",
COUNT(DISTINCT COUNTRY) AS Total_Unique_Countries
FROM MYTABLE
这应该给您所需的结果。
<details>
<summary>英文:</summary>
SELECT
COUNT(DISTINCT CASE WHEN YEAR=2020 THEN COUNTRY END) AS "2020",
COUNT(DISTINCT CASE WHEN YEAR=2021 THEN COUNTRY END) AS "2021",
COUNT(DISTINCT COUNTRY) AS Total_Unique_Countries
FROM MYTABLE
This should give you the result you are looking for.
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论