>>Hi,
>>
>>I am trying to get a report that would count based on the value in the BIT field (the DB is SQL Server).
>>
>>The table has the following fields
>>ID_VALUE CHAR(10)
>>COMPLETED BIT
>>
>>The SQL Select is as follows (simplified)
>>
>>select ID_VALUE, COUNT(COMPLETED) AS TOT_COMPLETED FROM MyTable Group by ID_VALUE
>>
>>
>>The problem is that the TOT_COMPLETED gets the value of Total Records in the Group. And I thought it would only get the number of records where the field COMPLETED has value of 1 (actually completed). I tried replacing COUNT(COMPLETED) with COUNT(COMPLETED=1) but I get an error.
>>
>>What am I missing?
>
>Try
>
>SUM(case when Completed = 1 then 1 else 0 end) as TotalCompleted --
>
>or alternatively
>
>COUNT(NULLIF(Completed,0)) as TotalCompleted -- count of null returns 0
>
>or also
>
>COUNT(case when Completed = 1 then Completed end) -- count of null returns 0
Thank you. I am sure all three cases will work but I tried the first one, SUM; it works and I stopped fighting success :)
"The creative process is nothing but a series of crises." Isaac Bashevis Singer
"My experience is that as soon as people are old enough to know better, they don't know anything at all." Oscar Wilde
"If a nation values anything more than freedom, it will lose its freedom; and the irony of it is that if it is comfort or money that it values more, it will lose that too." W.Somerset Maugham
