Selecting nth top row based on number of occurrences of value in 3 tables

Question

I have three tables let's say A, B and C. Each of them has column that's named differently, let's say D1, D2 and D3. In those columns I have values between 1 and 26. How do I count occurrences of those values and sort them by that count? Example:

TableA.D1
1
2
1
1
3

TableB.D2
2
1
1
1
2
3

TableC.D3
2
1
3

So the output for 3rd most common value would look like this:

3 -- number 3 appeared only 3 times

Likewise, output for 2nd most common value would be:

2 -- number 2 appeared 4 times

And output for 1st most common value:

1 -- number 1 appeared 7 times

Answer 1

You probably want :

select top (3) d1
from ((select d1 from tablea ta) union all
      (select d2 from tableb tb) union all
      (select d3 from tablec tc)
     ) t
group by d1
order by count(*) desc;

Answer 2

SELECT DQ3.X, DQ3.CNT
(
    SELECT DQ2.*, dense_rank() OVER (ORDER BY DQ2.CNT DESC) AS RN
        (SELECT DS.X,COUNT(DS.X) CNT FROM
                (select D1 as X FROM TableA UNION ALL SELECT D2 AS X FROM TABLE2 UNION ALL SELECT D3 AS X FROM TABLE3) AS DS
            GROUP BY DS.X
        ) DQ2
) DQ3 WHERE DQ3.RN = 3   --the third in the order of commonness - note that 'ties' can be handled differently

Answer 3

One of the things about SQL scripts: they get difficult to read very easily. I'm a big fan of making things as readable as absolute possible. So I'd recommend something like:

declare @topThree TABLE(entry int, cnt int)
select TOP 3 entry,count(*) as cnt
    from
    (
        select d1 as entry from tablea UNION ALL
        select d2 as entry from tableb UNION ALL
        select d3 as entry from tablec UNION ALL
    ) as allTablesCombinedSubquery
    order by count(*)
select TOP 1 entry
    from @topThree
    order by cnt desc

... it's extremely readable, and doesn't use any concepts that are tough to grok.