Problems displaying a SUM

[CGSB]

I'm having problems with the following query:

SELECT gcNo, yes_memberName, SUM(IIf(yes_memberName <> null,1,0)) as total_yes
FROM ballotReportData
Group by gcNo, yes_memberName;

I want to display the total amount of yes_memberNames that aren't null for each gcNo.

For example, I want the report to display something like this:

gcNo yes_memberName total_yes
2 name1 3
2 name2 3
2 name3 3
4 name1 2
4 2
4 name3 2

Currently, total_yes will appear as 1 for each row if there is a yes_memberName and 0 if there is no yes_memberName.

Please let me know if this is possible or if you need more information in order to help me.

 

[Crowley16]

to test if a column is null, you have to use "columnName is null"

sometimes it's actually better to use:
len(nz(colName, "")) > 0

--------------------
Procrastinate Now!

 

[CGSB]

Thanks for the reply. My SQL query, as is, can determine whether the value is null or not. It just doesn't sum up all the null values for each "gcNo". I want to be able to determine the sums of "yes_memberName" for each individual "gcNo", not each individual "yes_memberName" like my query is currently doing.

If you have any questions, or if I'm being unclear about what I want to accomplish, please let me know.

 

[PHV]

Something like this ?
SELECT gcNo, Sum(IIf(yes_memberName Is Null,1,0)) As total_null, Sum(IIf(yes_memberName Is Null,0,1)) As total_yes
FROM ballotReportData
GROUP BY gcNo

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886