CODEWITHSUNDEEP
sqlsql-server
Hammad Nasir
Hammad Nasir

Reputation: 129

Multiple conditional query to count records in SQL Server

Below is the current records in my SQL Server database , I want to create query perhaps a stored procedure which returns me count. As you can see there are three records against each AId in the current table.

At least two occurrences of same AId(e.g. 1) having IsComplete set to 1. If AId for 1 has three records and two of them are set to true(1) then it should count it otherwise skip that count. So according to my condition query should return me count 3 because AId 1, 3 and 4 has at least 1 IsComplete set to true. 

ID  PId SId Section                     IsComplete   AId

259 260 3   Area 1 - Items ( 1 - 4 )    1             1
260 260 4   Area 1 - Items ( 5 - 8 )    1             1
261 260 5   Area 1 - Items ( 9 - 12 )   0             1
262 260 6   Area 2 - Items ( 1 - 4 )    1             2
263 260 7   Area 2 - Items ( 5 - 8 )    0             2
264 260 8   Area 2 - Items ( 9 - 12 )   0             2
265 260 9   Area 3 - Items ( 1 - 4 )    1             3
266 260 10  Area 3 - Items ( 5 - 8 )    1             3
267 260 11  Area 3 - Items ( 9 - 12 )   0             3 
268 260 12  Area 4 - Items ( 1 - 4 )    1             4
269 260 13  Area 4 - Items ( 5 - 8 )    1             4
270 260 14  Area 4 - Items ( 9 - 12 )   0             4

Upvotes: 0

Views: 65

Answers (1)

shA.t
shA.t

Reputation: 16958

I think you mean this:

;WITH t as (
    SELECT *, COUNT(CASE WHEN IsComplete = 1 THEN AID END) OVER (PARTITION BY AID) cnt
    FROM yourTable
)    
SELECT COUNT(DISTINCT AID) yourCount
FROM t
WHERE cnt >= 2;

SELECT COUNT(DISTINCT AID) yourCount
FROM (
    SELECT *, COUNT(CASE WHEN IsComplete = 1 THEN AID END) OVER (PARTITION BY AID) cnt
    FROM yourTable) t
WHERE cnt >= 2;

Upvotes: 3

Related Questions