英文:
One SQL statement for multiple column totals based on year
问题
SELECTCOUNT(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_CountriesFROM 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 2US 2020 MA 3 6CA 2020 MAN 2 8CA 2020 ONT 5 1AU 2020 TAS 7 2AU 2020 VIC 3 3US 2021 NY 2 0US 2021 MA 8 2AU 2021 TAS 4 1AU 2021 VIC 5 2
I want my query to return this:
2020 | 2021 | Total_Unique_Countries3 2 3
I tried:
SELECTSUM(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_CountriesFROM MYTABLE GROUP BY YEAR
The result:
2020 | 2021 | Total_Unique_Countries6 0 30 4 2
答案1
得分: 0
在一个公用表达式（CTE）中，您可以首先消除重复项，然后进行计数。WITH CTE as (SELECT DISTINCT "Country", "Year" FROM MYTABLE)SELECTSUM(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_CountriesFROM 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**得分**: 0SELECTCOUNT(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_CountriesFROM MYTABLE这应该给您所需的结果。<details><summary>英文:</summary>SELECTCOUNT(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_CountriesFROM MYTABLEThis should give you the result you are looking for.</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论