Solved

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

Posted on 2014-03-10
6
212 Views
Last Modified: 2014-03-10
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
0
Comment
Question by:searchsanjaysharma
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39917034
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
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39917046
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
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 500 total points
ID: 39917067
Yes . right


either 0 or 1 it needs to have some value more than 1 times


Select count(*)
from mstm1

where

  (case when act is null then 0 else 1 end +
 case when act01  is null then 0 else 1 end +
 case when act14 is null then 0 else 1 end +
 case when act58 is null then 0 else 1 end +
 case when act914 is null then 0 else 1 end ) > 1
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 

Author Closing Comment

by:searchsanjaysharma
ID: 39917416
tx
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39917420
so mine was not working? curious
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39919687
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
0

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question