Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2014-03-10
6
Medium Priority
?
218 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
  • 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 1500 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

876 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