writing single query from multiple query

Question

i have a table reading with column

 [code]|[Test Mk]
  -----   -----
  1231  |  22.5
  1223  |  13.5
  1231  |  24.25
  1232  |  25.0
  ....    ....

when i Query like this

select [code], COUNT([code]) as Total 
from reading 
group by [code]

is shows this result

code    |Total
----     ----
1237    |728
1233    |698
1232    |701
1236    |651
1231    |655
1235    |626
1234    |636
1238    |685

again when i query like this

select [code], COUNT([code]) as FAIL 
from Reading 
where ROUND([Test Mk],0) < 24  
group by [code]

results

code    | FAIL
----    | ----
1237    | 617
1233    | 422
1232    | 60
1236    | 81
1231    | 271
1235    | 517
1234    | 149
1238    | 69

again when i query like this

select [code], COUNT([code]) as PASS 
from Reading 
where ROUND([Test Mk],0) >= 24  
group by [code]

result

code    | PASS
----    | ----
1237    | 111
1233    | 276
1232    | 641
1236    | 570
1231    | 384
1235    | 109
1234    | 487
1238    | 616

i want a single query which generate result in this format

[code] | [Total] | [PASS] | [FAIL]
----     ----       ----     ----
 1231  |  125   |  100   |  25
 1232  |  200   |  150   |  50

how do i do this

Pரதீப் · Accepted Answer · 2016-08-27 09:37:14Z

1

You need to use Conditional Aggregation

To get the PASS count, just count the rows only when ROUND([Test Mk],0) >= 24. This can be achieved like this.

Count(Case when ROUND([Test Mk],0) >= 24 then 1 END)

To get the FAIL count, just count the rows only when ROUND([Test Mk],0) < 24. This can be achieved like this.

Count(Case when ROUND([Test Mk],0) < 24 then 1 END)

Case statements generates 1 only when the row satisfies the condition else it will place NULL. Count aggregate skips the NULL values while counting

As a whole query

select [code], 
       COUNT([code]) as Total,
       Count(Case when ROUND([Test Mk],0) >= 24 then 1 END) as  PASS, 
       Count(Case when ROUND([Test Mk],0) < 24 then 1 END) as FAIL
from reading 
group by [code]

edited Aug 27, 2016 at 9:37

answered Aug 27, 2016 at 9:31

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

gofr1 · Accepted Answer · 2016-08-27 09:36:00Z

Another way with SUM:

SELECT  [code], 
        COUNT([code]) as [Total],
        SUM(CASE WHEN ROUND([Test Mk],0) >= 24 THEN 1 ELSE 0 END) as [PASS], 
        SUM(CASE WHEN ROUND([Test Mk],0) < 24 THEN 1 ELSE 0 END) as [FAIL]
FROM reading 
GROUP BY [code]

Yet another one (weird):

;WITH [code] As(
    select [code], COUNT([code]) as Total 
    from reading 
    group by [code]
), [fails] AS (
    select [code], COUNT([code]) as [FAIL]
    from Reading 
    where ROUND([Test Mk],0) < 24  
    group by [code]
), [passed] AS (
    select [code], COUNT([code]) as [PASS ]
    from Reading 
    where ROUND([Test Mk],0) >= 24  
    group by [code]
)

SELECT  c.[code],
        c.[Total],
        f.[FAIL],
        p.[PASS]
FROM [code] c
LEFT JOIN [fails] f
    ON c.[code] = f.[code]
LEFT JOIN [passed] p
    ON c.[code] = p.[code]

Collectives™ on Stack Overflow

writing single query from multiple query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related