同一张表的不同列的计数总和

小编典典

同一张表的不同列的计数总和

sql

下表给出的表称为

+---+---+
| x | y |
+---+---+
| 1 | 2 |
| 1 | 5 |
| 5 | 2 |
| 5 | 1 |
+---+---+

我想要以下结果的sql查询

+----+-------------+
| id | count_total |
+----+-------------+
|  1 |           3 |
|  2 |           2 |
|  5 |           3 |
+----+-------------+

注意：我能够分别计算每个ID的行数，但无法获得相同ID的总和。所以我想在一个查询中合并或获取以下查询的总和。

SELECT x, count(*) as total_x FROM table GROUP BY x
SELECT y, count(*) as total_y FROM table GROUP BY y

阅读 207

2021-05-16

共1个答案

小编典典

尝试：

SELECT
A.ID, SUM(A.COUNTS) AS COUNT_TOTAL
FROM
(
SELECT X AS ID, COUNT(*) AS COUNTS FROM TABLE1 GROUP BY X
UNION ALL
SELECT Y AS ID, COUNT(*) AS COUNTS FROM TABLE1 GROUP BY Y
) A
GROUP BY A.ID
ORDER BY A.ID;

2021-05-16