Hammad Nasir Hammad Nasir - 5 months ago 7
SQL Question

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

Answer

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;