+4 votes
1 view
in SQL by (20.3k points)

I'm building a query with a GROUP BY clause that needs the ability to count records based only on a certain condition (e.g. count only records where a certain column value is equal to 1).

SELECT  UID, 

        COUNT(UID) AS TotalRecords, 

        SUM(ContractDollars) AS ContractDollars,

        (COUNTIF(MyColumn, 1) / COUNT(UID) * 100) -- Get the average of all records that are 1

FROM    dbo.AD_CurrentView

GROUP BY UID

HAVING  SUM(ContractDollars) >= 500000

The COUNTIF() line obviously fails since there is no native SQL function called COUNTIF, but the idea here is to determine the percentage of all rows that have the value '1' for MyColumn.

Any thoughts on how to properly implement this in an MS SQL 2005 environment?

3 Answers

+4 votes
by (40.3k points)

You can use the SUM (not COUNT!) combined with the CASE statement, this way:

SELECT SUM(CASE WHEN myColumn=1 THEN 1 ELSE 0 END)

FROM AD_CurrentView

Note: NULLs can be environment-dependent. 

You can handle nulls like this:

SELECT SUM(CASE WHEN ISNULL(myColumn,0)=1 THEN 1 ELSE 0 END)

FROM AD_CurrentView

Learn SQL commands with the help of these  SQL Commands Cheat Sheet.

by (44.6k points)
This helped solve my issue
by (16.3k points)
Worked for me.
by (38.2k points)
This resolved my issue perfectly.
+2 votes
by (92.8k points)

I usually do what @Soni Kumari recommended, but brainstormed and tested a slightly hokey alternative that I felt like sharing.

You can take advantage of the fact that COUNT(ColumnName) doesn't count NULLs, and use something like this:

SELECT COUNT(NULLIF(0, myColumn))

FROM AD_CurrentView

NULLIF - returns NULL if the two passed in values are equivalent.

Advantage: Shows your intent to COUNT rows instead of having the SUM() notation.

Disadvantage: Not as explicit how it is working ("magic" is usually bad).

by (19.8k points)
It worked for me!
by (47.2k points)
I had to use COUNTIF() in my case as part of my SELECT columns AND to mimic a % of the number of times each item appeared in my results.
by (31.6k points)
Both the answers of @soni and @vinita were helpful and solved the issue.
+3 votes
by (29.8k points)



I would use the following syntax because with this the advantage is that it is  ANSI complient and not tied to a particular database vendor.

select count(case when myColumn = 1 then 1 else null end)
from   AD_CurrentView


 

by (25.3k points)
This answer is best because it helps to get the idea of "counting rows"  and is more extensible since you can use any comparison operator; not just =. I'm using it for "count the number of responses >=2".
by (33.2k points)
It worked for me.
Thanks!
by (28.1k points)
Nice explanation.
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...