Counting from 2 columns in SQL server

Question

In essence I have one table with two columns

One    Two    
-----------
A       B   
B       C    
C       D

and I want to count the number of A's through D.

Resulting

Letter    Count  
---------------
   A        1
   B        2  
   C        2   
   D        1

My code now is

Select one, count("") from table   
group by one   
union    
Select two, count("*") from table  
group by two

Right now I am getting

Letter    Count  
---------------
   A        1
   B        1  
   B        1  
   C        1  
   C        1  
   D        1

How do I fix it?

score 3 · Accepted Answer · answered Oct 21 '14 at 10:03

3

Try this

SELECT Count(a), 
       a 
FROM  (SELECT cola a 
       FROM   table 
       UNION ALL 
       SELECT colb a 
       FROM   table) c 
GROUP  BY a

answered Oct 21 '14 at 10:03

Pரதீப்

91,748
19
131
172

2

Not sure why anyone would vote this down as it's a correct solution. – jpw Oct 21 '14 at 10:15
@Pradeeep you can use UNION instead of UNION ALL Bcoz UNION is much more efficient than UNION ALL. – Hardik Parmar Oct 21 '14 at 10:29
@Hardik union will remove duplicates that should not be done here bud!! – Pரதீப் Oct 21 '14 at 10:31
If I want to save it as a new table? Create Table (name) insert into (name) From (SQL Query) doesnt work. Is there a way to do this? – polka Oct 21 '14 at 10:34
1

@bropedope - Simple way is **select count(a),a into newtable from (select ..) group by a** – Pரதீப் Oct 21 '14 at 10:37
1

@Hardik `union all`is typically faster as it does not have to remove duplicates. – jpw Oct 21 '14 at 10:38
@jpw union all is slower then UNION. I have read alot of articals related to this. if you want you can check with the execution plan for this Please refer this article http://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all – Hardik Parmar Oct 21 '14 at 10:49
@Hardik - From your link it is very clear that UNION ALL much Faster than UNION!!!!! Union has to sort the records first then it has to remove duplicates. sort is a costlier operation. – Pரதீப் Oct 21 '14 at 10:54
@Hardik I think you should read the article you linked again. It clearly says that `union all` is faster than `union` just like I said. The execution plan shows that`union`will use a costly sort or merge join to exclude duplicates which `union all` won't have to do. – jpw Oct 21 '14 at 11:00

user3616725 · Answer 2 · 2014-10-21T10:34:05.133

1

there is no reason to group twice.

select letter, count(*) as total_cnt
from 
(
Select one as letter from table
union all
Select two as letter from table
) 
group by letter;

edited Oct 21 '14 at 10:34

answered Oct 21 '14 at 10:04

user3616725

3,485
1
18
27

2

@bropedope It's actually incorrect as `union`without`all`eliminates duplicates. – jpw Oct 21 '14 at 10:15
@jpw Never knew that! – user3616725 Oct 21 '14 at 10:34

Donal · Answer 3 · 2014-10-21T10:18:09.553

1

select letter, sum(total) from
(
Select one as letter, count(1) as total from tablename
group by one
union all
Select two as letter, count(1) as total from tablename
group by two) as t1
group by t1.letter 
order by t1.letter asc

edited Oct 21 '14 at 10:18

answered Oct 21 '14 at 10:04

Donal

31,121
10
63
72

score 0 · Answer 4 · answered Oct 21 '14 at 10:46

0

You can do this

SELECT LETTER, count(*) AS _count FROM ( SELECT One AS Letter FROM Test UNION ALL SELECT Two AS Letter FROM Test ) T1 GROUP BY LETTER

answered Oct 21 '14 at 10:46

Nui_CpE

76
6

Counting from 2 columns in SQL server

4 Answers4