Solved

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

Posted on 2014-03-10
6
213 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
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…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

617 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