CODEWITHSUNDEEP
sqlsql-server
Vikas
Vikas

Reputation: 199

Need help in combining two sql queries into 1

Query 1 : 

SELECT DISTINCT sysnam,COUNT(*) AS High FROM tablename
WHERE Label00002 = '1.High' AND Item_grade = 'NON-COMPLIANT'
GROUP BY sysnam

Output:

SYSNAME,High

         A,12

         B,13

Query 2:

SELECT DISTINCT sysnam,count(*) AS Medium FROM tablename
WHERE Label00002 = '2.Medium' AND Item_grade = 'NON-COMPLIANT'
GROUP BY sysnam

Output: SYSNAME,Medium

         A,10

         B,11

How can I combine both the above queries into one and would like to see the output as below

Output:

SYSNAME,High,Medium

       A,12,10

       B,13,11

Upvotes: 0

Views: 67

Answers (3)

Valerica
Valerica

Reputation: 1630

Put the queries in two CTEs and join the results

;with 
cte_qry1 as (
    select distinct sysnam,count(*) as High from tablename
    where Label00002 = '1.High' and Item_grade = 'NON-COMPLIANT'
    group by sysnam
)
cte_qry2 as (
    select distinct sysnam,count(*) as Medium from tablename
    where Label00002 = '2.Medium' and Item_grade = 'NON-COMPLIANT'
    group by sysnam
)
select cte_qry1.sysnam, High, Medium
from cte_qry1
inner join cte_qry2
    on cte_qry1.sysnam = cte_qry2.sysnam

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133400

You could use a join on subselect based on same sysnam

select distinct t1.sysnam,count(t1.*) as High, t2.Medium 
from tablename t1
left join (
    select distinct sysnam,count(*) as Medium from tablename
    where Label00002 = '2.Medium' and Item_grade = 'NON-COMPLIANT'
    group by sysnam
  ) t2 on t2.sysnam = t1.sysnam
where t1.Label00002 = '1.High' 
and t1.Item_grade = 'NON-COMPLIANT'
group by sysnam

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Use conditional aggregation:

select sysnam, 
       sum(case when Label00002 = '1.High' then 1 else 0 end) as high,
       sum(case when Label00002 = '2.Medium' then 1 else 0 end) as medium
from tablename
where Item_grade = 'NON-COMPLIANT' 
group by sysnam;

Note: It is almost never correct to use select distinct when you have a group by.

Upvotes: 1

Related Questions