CODEWITHSUNDEEP
sql
John Fowler
John Fowler

Reputation: 63

Using count with a union query

This should be fairly straightforward but I can't find the exact syntax I need. I have a table which has a name1 and a name2 field. I just want a list of all the unique values in the fields and a count for each value. Eg:

name1       name2
-----------------
smith       jones
jones       williams
evans       williams
elliot      james
williams    smith

And I want:

name     count
--------------
smith    2
jones    2
Williams 3
evans    1
Elliot   1
james    1

To get the unique names I used a union query:

select name1 as myname
  from myTable
 union
select name2 as myname
  from myTable

But I can't find the right syntax for where the count goes. Can someone please put me out of my misery?

Upvotes: 2

Views: 7313

Answers (4)

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186668

Yet another typical way of solving such problems is to use with syntax:

-- Put here whatever entangled query you have and mark it "with"
with myQuery as 
  (select name1 as myName
     from myTable
    union all
    select name2 as myName
      from myTable)

-- And now do everything you want with myQuery, as if it's an ordinary table
  select myName as Name,
         Count(*)
    from myQuery
group by myName

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use union all:

select name, count(*) as cnt
from ((select name1 as name from t) union all
      (select name2 from t)
     ) t
group by name;

It is very important to use union all instead of union. The latter removes duplicates, which you do not want.

For larger queries of this sort, it is more efficient to use unpivot, or the following construct:

select (case when n.n = 1 then name1 else name2 end) as name, count(*)
from t cross join
     (select 1 as n union all select 2) n
group by (case when n.n = 1 then name1 else name2 end)

The union all query will scan the table twice. This version will generally only scan the table once.

Upvotes: 2

Prahalad Gaggar
Prahalad Gaggar

Reputation: 11599

select t.name,count(t.name) count
from 
(
select name1 as name from tbl
union all 
select name2 as name from tbl
)t
group by t.name

SQL Fiddle

Upvotes: 1

fthiella
fthiella

Reputation: 49049

You should use UNION ALL instead of UNION, because UNION will remove all duplicates while UNION ALL will not. And then you can use your union query as a subquery, and do the counts:

SELECT name, COUNT(*)
FROM
  (SELECT name1 name FROM tablename
   UNION ALL
   SELECT name2 name FROM tablename) s
GROUP BY
  name

Please see fiddle here.

Upvotes: 6

Related Questions