Selecting multiple counted fields in T-SQL query

Question

I am stuck on a SQL query, where I need to count the number of different values in a field.

My query is:

SELECT 
     Area
     , (SELECT count(TestResult)  FROM TestRun WHERE TestResult = 'PASS' AND TestRun.CreatedDate > '2019-11-18 01:00:00') as [Passed]
     , (SELECT count(TestResult)  FROM TestRun WHERE TestResult = 'FAIL' AND TestRun.CreatedDate > '2019-11-18 01:00:00') as [Failed]
     , (SELECT count(TestResult)  FROM TestRun WHERE TestResult = 'NOTRUN' AND TestRun.CreatedDate > '2019-11-18 01:00:00') as [NotRun]
FROM TestRun
WHERE dbo.TestRun.CreatedDate > '2019-11-18 01:00:00'
GROUP BY dbo.TestRun.Area, TestRun.TestResult, TestRun.CreatedDate

But The Results I get back look like this, instead of individual values:

I see, the answer is actually correct, but it's posting the aggregated value for each record it finds. How would I see one row of data for each area? (There are multiple areas, fyi) — Su Llewellyn
– Su Llewellyn, Commented Nov 18, 2019 at 19:55

kjmerf · Accepted Answer · 2019-11-18 21:10:50Z

5

You can create your own fields using case and then sum them.

SELECT Area
, SUM(CASE WHEN TestResult = 'PASS' THEN 1 ELSE 0 END) AS Passed
, SUM(CASE WHEN TestResult = 'FAIL' THEN 1 ELSE 0 END) AS Failed
, SUM(CASE WHEN TestResult = 'NOTRUN' THEN 1 ELSE 0 END) AS NotRun
FROM TestRun
WHERE CreatedDate > '2019-11-18 01:00:00'
GROUP BY Area

answered Nov 18, 2019 at 21:10

kjmerf

4,3853 gold badges24 silver badges29 bronze badges

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

2 Comments

Eric Brandt Over a year ago

And this will be orders of magnitude faster than running sub-queries.

HABO Over a year ago

Aside: The term of art for this is conditional aggregation.

Collectives™ on Stack Overflow

Selecting multiple counted fields in T-SQL query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related