Solved

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

Posted on 2014-03-10
6
204 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 142

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Closing Comment

by:searchsanjaysharma
ID: 39917416
tx
0
 
LVL 142

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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

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 I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now