Sandeep Sandeep - 3 years ago 113
SQL Question

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

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
enter image description here

Sample out put
enter image description here

Answer Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download