asked on

How to count the no. of rows as 1 for the following example

I have a table mstm1
where there are fields
uin name act act01 act14 act58 act914 adult
1 aa 0 1 0 0 0 0
2 bb 1
3 cc 0 0 1 1 0 0
4 dd 0 0 0
5 ee 1 0 1 1 0

How to get the rowcount as 4 if any of the act fields is >=1

Pratima

Do you mean to say sum of act files is greater than 1 or
either 0 or 1 it needs to have some value more than 1 times

for
sum of act files is greater than 1
Try this
Select count(*) from mstm1

where sum (act + act01 + act14 + act58 + act914) > 1

Guy Hengel [angelIII / a3]

SUM() cannot be used here, as we need to check the data row-wise.
also, SUM() could not be in the WHERE clause, only in HAVING clause.

so, you need to check like this:

Select count(*) 
from mstm1
where  isnull(act,0) + isnull(act01,0) + isnull(act14,0) + isnull(act58,0) + isnull(act914,0) > 1

Open in new window

at least, for a "one-off" query. if you need to do this regularly, I would recommend you implement a computed field with that expression, eventually indexed so it would be maintained all the time automatically, and query that field

ASKER CERTIFIED SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

searchsanjaysharma

ASKER

Guy Hengel [angelIII / a3]

so mine was not working? curious

Pratima

your solution is correct for situation sum of act files is greater than 1

I have provided second solution for either 0 or 1 it needs to have some value more than 1 times