CODEWITHSUNDEEP
sqlcountsummultiple-columns
Halit Sakca
Halit Sakca

Reputation: 23

sum of counts of different columns of same table

Given below table called table 

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

I want to have sql query for the following results

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

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

Upvotes: 2

Views: 6781

Answers (4)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

Probably the easiest way is to select all x and all y and then aggregate over them.

select id, count(*) as count_total 
from (select x as id from mytable union all select y from mytable) ids
group by id
order by id;

Upvotes: 0

kumar
kumar

Reputation: 1

declare @table table ( x int, y int ) insert into @table select 1,2 union all select 1,5 union all select 5,2 union all select 5,1 select x,SUM(a) from ( select x,COUNT() as a from @table group by x union all select y,COUNT() as a from @table group by y ) a group by x

Upvotes: 0

Vash
Vash

Reputation: 1787

Try: 

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;

Upvotes: 4

Muhammad Soliman
Muhammad Soliman

Reputation: 23756

You can use union to bring them together, I did not try it actually but it should work fine. if it did not work please leave a comment and I would be happy to help and edit my answer. 

select u.x, sum(u.total) 
from 
(
(SELECT x as x, count(*) as total FROM table GROUP BY x) 
union all 
(SELECT y as x, count(*) as total FROM table GROUP BY y)  
) as u 
group by u.x

Upvotes: 0

Related Questions