How to perform an arithmetic operation on the findings of 2 queries

Question

Below are 2 queries:

SELECT COUNT(BUG.BG_BUG_ID) AS BUG_ID_SIT_CNT 
FROM BUG 
WHERE BUG.BG_USER_02 = 'SIT'

SELECT COUNT(BUG.BG_BUG_ID) AS BUG_ID_UAT_CNT 
FROM BUG 
WHERE BUG.BG_USER_02 = 'UAT'

How to write a query to use the results from the above queries and perform the following operation:

Defect removal efficiency: BUG_ID_SIT_CNT/(BUG_ID_SIT_CNT + BUG_ID_UAT_CNT)

Answer 1

SELECT BUG_ID_SIT_CNT * 1. / BUG_ID_ALL
FROM (
    SELECT
        COUNT(CASE WHEN BG_USER_02 = 'SIT' THEN BG_BUG_ID END) AS BUG_ID_SIT_CNT,
        COUNT(BG_BUG_ID) AS BUG_ID_ALL
    FROM BUG
    WHERE BG_USER_02 IN ('SIT', 'UAT')
) t

Answer 2

You can get it in an external request, for example

SELECT BUG_ID_SIT_CNT/(BUG_ID_SIT_CNT + BUG_ID_UAT_CNT) as testcol
FROM (
SELECT SUM(CASE WHEN BUG.BG_USER_02 = 'SIT' THEN 1 ELSE 0 END) AS BUG_ID_SIT_CNT,
SUM(CASE WHEN BUG.BG_USER_02 = 'UAT' THEN 1 ELSE 0 END) AS BUG_ID_UAT_CNT 
FROM BUG ) xx;

Answer 3

WITH SELECT.

Answer 4

WITH sit
AS
(
    SELECT COUNT(BUG.BG_BUG_ID) AS BUG_ID_SIT_CNT 
    FROM BUG 
    WHERE BUG.BG_USER_02 = 'SIT'
),
uat
AS
(
    SELECT COUNT(BUG.BG_BUG_ID) AS BUG_ID_UAT_CNT 
    FROM BUG 
    WHERE BUG.BG_USER_02 = 'UAT'
)
SELECT sit.BUG_ID_SIT_CNT/(sit.BUG_ID_SIT_CNT + uat.BUG_ID_UAT_CNT) 
FROM sit, uat

Answer 5

, :

DECLARE @BUG_ID_SIT_CNT AS INT = 
(SELECT COUNT(BUG.BG_BUG_ID)
FROM BUG 
WHERE BUG.BG_USER_02 = 'SIT')

DECLARE @BUG_ID_UAT_CNT AS INT =
(SELECT COUNT(BUG.BG_BUG_ID)
FROM BUG 
WHERE BUG.BG_USER_02 = 'UAT')

SELECT @BUG_ID_SIT_CNT/(@BUG_ID_SIT_CNT + @BUG_ID_UAT_CNT)

Answer 6

SELECT cast(BUG_ID_SIT_CNT as decimal) / cast((BUG_ID_SIT_CNT + BUG_ID_UAT_CNT)as decimal) 
FROM (
 SELECT
    COUNT(CASE WHEN BG_USER_02 = 'SIT' THEN BG_BUG_ID END) AS BUG_ID_SIT_CNT
    COUNT(CASE WHEN BG_USER_02 = 'UAT' THEN BG_BUG_ID END) AS BUG_ID_UAT_CNT
FROM BUG
WHERE BG_USER_02 IN ('SIT', 'UAT')
) t