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

本文介绍了同一张表的不同列的计数总和的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

在名为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;