2

I'm trying to sum the results of following three queries on same table to generate one single report.

SELECT b.BG_SEVERITY as Severity, count (b.BG_BUG_ID) as Total
FROM BUG b where
 b.BG_USER_12='QA3'
 group by b.BG_SEVERITY

SELECT s.BG_SEVERITY as Severity, count (s.BG_BUG_ID) as Total
FROM BUG s where
 s.BG_USER_12='QA1'
 group by s.BG_SEVERITY

SELECT a.BG_SEVERITY as Severity, count (a.BG_BUG_ID) as Total
FROM BUG a where
 a.BG_USER_12='QA2'
 group by a.BG_SEVERITY

Result from each query will look like below:

Severity   Total
1-Critical  4
2-High      94
3-Medium    23
4-Low       45

I'm looking to combine / sum up the 'total' column from the three queries above. What feature of SQL can I use to do this?

vnai
  • 31
  • 2

2 Answers2

2

If using sql-server you can try to use union. something like this:

SELECT
    Severity,
    SUM(Total)
FROM
    (
    SELECT b.BG_SEVERITY as Severity, count (b.BG_BUG_ID) as Total FROM BUG b where b.BG_USER_12='QA3' group by b.BG_SEVERITY
    UNION ALL
    SELECT s.BG_SEVERITY as Severity, count (s.BG_BUG_ID) as Total FROM BUG s where s.BG_USER_12='QA1' group by s.BG_SEVERITY    
    UNION ALL 
    SELECT a.BG_SEVERITY as Severity, count (a.BG_BUG_ID) as Total FROM BUG a where a.BG_USER_12='QA2' group by a.BG_SEVERITY
    ) tbl
GROUP BY
    Severity
user1626711
  • 66
  • 2
2

You can use IN in your condition.

SELECT   b.BG_SEVERITY as Severity, 
         COUNT(b.BG_BUG_ID) as Total
FROM     BUG b 
WHERE    b.BG_USER_12 IN ('QA3','QA1','QA2')
GROUP BY b.BG_SEVERITY
John Woo
  • 258,903
  • 69
  • 498
  • 492