How to use union of two queries in SQL that has order by and count()

Question

I have following two queries how do i use union so as to see both the results in the single query execution

select TOP 1 AGE, DIAGNOSIS_CODE_1, count(DIAGNOSIS_CODE_1) as total_count
from Health
where age = 7
group by AGE, DIAGNOSIS_CODE_1
order by total_count DESC;

select TOP 1 AGE, DIAGNOSIS_CODE_1, count(DIAGNOSIS_CODE_1) as total_count
from Health
where age = 9
group by AGE, DIAGNOSIS_CODE_1
order by total_count DESC;

Sample out put

`select * from (select TOP 1 AGE, DIAGNOSIS_CODE_1, count(DIAGNOSIS_CODE_1) as total_count from Health where age = 7 group by AGE, DIAGNOSIS_CODE_1 union all select TOP 1 AGE, DIAGNOSIS_CODE_1, count(DIAGNOSIS_CODE_1) as total_count from Health where age = 9 group by AGE, DIAGNOSIS_CODE_1 )x order by x.total_count desc` — vhadalgi, Feb 12 '16 at 05:29
Possible duplicate of [SQL Server: How to use UNION with two queries that BOTH have a WHERE clause?](http://stackoverflow.com/questions/5426767/sql-server-how-to-use-union-with-two-queries-that-both-have-a-where-clause) — venkat, Feb 12 '16 at 05:41
@Sandeep - Please give some sample data with expected output of both queries. — Utsav, Feb 12 '16 at 05:45
@All - Please understand the question. The `Top 1` will not give expected result without `order by` so doing a union on top of that is of no use. OP should clearly give some sample data before we can come up with an answer. — Utsav, Feb 12 '16 at 05:47
Added screen shot of individual queries in the question. Please have a look at it. — Sandeep, Feb 12 '16 at 05:55
@Sandeep - Added this [answer](http://stackoverflow.com/a/35355977/5234334). Check it. — Utsav, Feb 12 '16 at 06:13

Praveen · Answer 1 · 2016-02-12T05:55:16.693

You can do this by row_number() over(partition by.. like,

select
    AGE, 
    DIAGNOSIS_CODE_1, 
    total_count
from (
    select  
        AGE, 
        DIAGNOSIS_CODE_1, 
        count(DIAGNOSIS_CODE_1) as total_count,
        row_number() over (partition by AGE order by count(DIAGNOSIS_CODE_1) desc) rnk
    from Health
    where age in (7, 9)
    group by AGE, DIAGNOSIS_CODE_1
) x
where rnk = 1

Or you can use union all like;

with tmp_1 as (
    select TOP 1 AGE, DIAGNOSIS_CODE_1, count(DIAGNOSIS_CODE_1) as total_count
    from Health
    where age = 7
    group by AGE, DIAGNOSIS_CODE_1
    order by total_count DESC
),
tmp_2 as (
    select TOP 1 AGE, DIAGNOSIS_CODE_1, count(DIAGNOSIS_CODE_1) as total_count
    from Health
    where age = 9
    group by AGE, DIAGNOSIS_CODE_1
    order by total_count DESC
)
select AGE, DIAGNOSIS_CODE_1, total_count from tmp_1
union all
select AGE, DIAGNOSIS_CODE_1, total_count from tmp_2

I was thinking can we use your first solution for ages between 1 to 20? If we follow the second solution query becomes bigger and bigger. If there is a way let me know how to do it. — Sandeep, Feb 13 '16 at 17:47
Change where condition from `age in (7,9)` to `age > 1 and age < 20` — Praveen, Feb 13 '16 at 17:53

Shakeer Mirza · Accepted Answer · 2016-02-12T05:54:41.563

1

Just add UNION ALL in between those queries. The ORDER BY clause wont accept when UNION ALL applied. So i concluded it by taking them in a inner set.

    SELECT * FROM (

    SELECT TOP 1 AGE, DIAGNOSIS_CODE_1, COUNT(DIAGNOSIS_CODE_1) AS TOTAL_COUNT
    FROM HEALTH
    WHERE AGE = 7
    GROUP BY AGE, DIAGNOSIS_CODE_1


    UNION ALL

    SELECT TOP 1 AGE, DIAGNOSIS_CODE_1, COUNT(DIAGNOSIS_CODE_1) AS TOTAL_COUNT
    FROM HEALTH
    WHERE AGE = 9
    GROUP BY AGE, DIAGNOSIS_CODE_1

    )AS A
    ORDER BY TOTAL_COUNT DESC;

As per the case you can go this way. If your case is to give order separately, then you can give order by in inner set.

    SELECT * FROM (

    SELECT TOP 1 AGE, DIAGNOSIS_CODE_1, COUNT(DIAGNOSIS_CODE_1) AS TOTAL_COUNT
    FROM HEALTH
    WHERE AGE = 7
    GROUP BY AGE, DIAGNOSIS_CODE_1
    ORDER BY TOTAL_COUNT DESC;
    )AS B

    UNION ALL

    SELECT * FROM (
    SELECT TOP 1 AGE, DIAGNOSIS_CODE_1, COUNT(DIAGNOSIS_CODE_1) AS TOTAL_COUNT
    FROM HEALTH
    WHERE AGE = 9
    GROUP BY AGE, DIAGNOSIS_CODE_1
    ORDER BY TOTAL_COUNT DESC;
    )AS A

edited Feb 12 '16 at 05:54

answered Feb 12 '16 at 05:33

Shakeer Mirza

5,054
2
18
41

this will not give `max age` as fetched by `order by desc` clause for both age group – Utsav Feb 12 '16 at 05:39
But here his question is different i think.. @Utsav However we cant incluse ORDER BY in inside a view like moves.. – Shakeer Mirza Feb 12 '16 at 05:42
Thats why we should wait for OP to give sample data without which it is difficult to assume things. – Utsav Feb 12 '16 at 05:47
You cannot use `order by` in subqueries – Utsav Feb 12 '16 at 05:54
I have checked with my existing data. Its working fine @Utsav – Shakeer Mirza Feb 12 '16 at 05:55

score 0 · Answer 3 · edited Feb 12 '16 at 05:45

0

If you wish to use union you can try the following query .

(select TOP 1 AGE, DIAGNOSIS_CODE_1, count(DIAGNOSIS_CODE_1) as total_count
from Health
where age = 7
group by AGE, DIAGNOSIS_CODE_1 ) union
(select TOP 1 AGE, DIAGNOSIS_CODE_1, count(DIAGNOSIS_CODE_1) as total_count
from Health
where age = 9
group by AGE, DIAGNOSIS_CODE_1) order by total_count DESC;

And for this particular query, I would suggest you to use where age IN (7,9) which would reduce the effort .

edited Feb 12 '16 at 05:45

TT.

15,774
6
47
88

answered Feb 12 '16 at 05:44

Anudeep Hegde

1
2

This gives NULL in total_count. – Sandeep Feb 12 '16 at 06:02
oops . I got the critical part wrong . Looks like we have couple of good solutions already . cheers – Anudeep Hegde Feb 12 '16 at 09:57

score 0 · Answer 4 · answered Feb 12 '16 at 05:45

When any SELECT statement in a UNION statement includes an ORDER BY clause, that clause should be placed after all SELECT statements.Using UNION of two SELECT statements with ORDER BY

SELECT * FROM (
                      SELECT TOP 1 AGE, DIAGNOSIS_CODE_1, count(DIAGNOSIS_CODE_1) as total_count
                      FROM Health
                      WHERE Age = 7
                      GROUP BY AGE, DIAGNOSIS_CODE_1

                     UNION ALL    

                      SELECT TOP 1 AGE, DIAGNOSIS_CODE_1, count(DIAGNOSIS_CODE_1) as total_count
                      FRPM Health
                      WHERE Age = 9
                      GROUP BY AGE, DIAGNOSIS_CODE_1
) AS T

ORDER BY total_count DESC;

score 0 · Answer 5 · answered Feb 12 '16 at 06:12

You have to use something like this. I am not using union but getting count of 2 different age groups and then selecting max count among them

SQLFiddle Demo

SELECT t1.age, 
       t1.diagnosis_code_1, 
       t1.total_count 
FROM   (SELECT t.*, 
               Rank() 
                 OVER ( 
                   partition BY age 
                   ORDER BY total_count DESC) AS tc 
        FROM   (SELECT age, 
                       diagnosis_code_1, 
                       Count(diagnosis_code_1) AS total_count 
                FROM   health 
                WHERE  age IN ( 7, 9 ) 
                GROUP  BY age, 
                          diagnosis_code_1
                ) t
        ) t1 
WHERE  t1.tc = 1

You can use the same query and modify or remove WHERE age IN ( 7, 9 ) clause to get count for more age groups.

How to use union of two queries in SQL that has order by and count()

5 Answers5