同一张表的不同列的计数总和 [英] sum of counts of different columns of same table
本文介绍了同一张表的不同列的计数总和的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
在名为table的表下面给出
Given below table called table
+---+---+
| x | y |
+---+---+
| 1 | 2 |
| 1 | 5 |
| 5 | 2 |
| 5 | 1 |
+---+---+
我想对以下结果进行sql查询
I want to have sql query for the following results
+----+-------------+
| id | count_total |
+----+-------------+
| 1 | 3 |
| 2 | 2 |
| 5 | 3 |
+----+-------------+
注意: 我能够分别计算每个ID的行数,但无法获得相同ID的总和. 所以我想在单个查询中合并或获取以下查询的总和.
Note: I was able to count separately the rows per id but I could not get the sum for the same id. so I want to combine or get sum of below queries in a single query.
SELECT x, count(*) as total_x FROM table GROUP BY x
SELECT y, count(*) as total_y FROM table GROUP BY y
推荐答案
尝试:
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;
这篇关于同一张表的不同列的计数总和的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文