我有資料看起來像這樣的表格。我正在尋找每個TYPE反對的總數DATE。
因此，對于上述資料輸出將是：
我嘗試了以下查詢，但結果包含多次計算的重復項。
SELECT
DATE,
SUM(CASE WHEN TYPE = 'Existing' THEN 1 ELSE 0 END) AS "EXISTING",
SUM(CASE WHEN TYPE = 'New' THEN 1 ELSE 0 END) AS "NEW"
FROM
mytab
WHERE
DATE >= '2022-01-01'
GROUP BY
DATE ;
上述查詢的輸出：
uj5u.com熱心網友回復：
未經測驗，但也許你想要什么：
SELECT
DATE,
COUNT(DISTINCT CASE WHEN TYPE = 'Existing' THEN ID END) AS "EXISTING",
COUNT(DISTINCT CASE WHEN TYPE = 'New' THEN ID END) AS "NEW"
FROM
mytab
WHERE
DATE >= '2022-01-01'
GROUP BY
DATE ;
uj5u.com熱心網友回復：
一種方法是在子查詢之上進行條件聚合，該子查詢首先只從原始表中獲取不同的記錄。
SELECT
DATE,
COUNT(CASE WHEN TYPE = 'Existing' THEN 1 END) AS "EXISTING",
COUNT(CASE WHEN TYPE = 'New' THEN 1 END) AS "NEW"
FROM (SELECT DISTINCT ID, TYPE, DATE FROM mytab) t
WHERE DATE >= '2022-01-01'
GROUP BY DATE;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/405623.html
標籤：