CODEWITHSUNDEEP
sqlms-access
JT2013
JT2013

Reputation: 643

MS Access SQL - Count # of Records = TRUE

I have what seems fairly simple but am missing something here. I am trying to display all distinct [Business Process] values, and count the number of records for each [Business Process] in which [Corrective Action Is Required] = TRUE. There could be potentially zero records for some [Business Process] values that have [Corrective Action Is Require] = TRUE, or there could be values > 0. This is the SQL that I am using:

SELECT  [Business Process], sum([Corrective Action Is Required]) as [Total Corrective Action Is Required]

FROM [Review Results]

WHERE [Corrective Action is Required] in 

(select IIF([Corrective Action Is Required] = TRUE, 1, 0) from [Review Results]) 

GROUP BY [Business Process]

ORDER BY [Business Process];

Currently the SQL is showing a value of 0 in all records for [Total Corrective Action Is Required]. Can someone pin point what I am not doing correctly?

Upvotes: 1

Views: 2828

Answers (3)

DJ.
DJ.

Reputation: 16257

You can sum a boolean but in access you will get negative results as True = -1 - so this should work

SELECT  [Business Process], sum(iif([Corrective Action Is Required] = TRUE, 1, 0)) as [Total Corrective Action Is Required]
FROM [Review Results]
GROUP BY [Business Process]
ORDER BY [Business Process]

Upvotes: 1

Fionnuala
Fionnuala

Reputation: 91376

In Access, True=-1 and False=0, so how about:

SELECT [Business Process], 
       Sum(Abs([Corrective Action Is Required])) 
          as [Total Corrective Action Is Required]
FROM [Review Results]
GROUP BY [Business Process]

You do not need Order By when you have Group By.

Upvotes: 3

jclozano
jclozano

Reputation: 628

Wouldn't it be easier to do this?, also remember to check your underlying data.

SELECT  [Business Process], count([Corrective Action Is Required]) as [Total Corrective Action Is Required]
FROM [Review Results]
WHERE [Corrective Action Is Required] = 1
GROUP BY [Business Process]
ORDER BY [Business Process];

Upvotes: 0

Related Questions