Solved

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

Posted on 2014-03-10
6
206 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql Permission 6 52
SQL Query Syntax:  Why is the CTE Pulling in More Data Than Asked For? 5 57
Analysis of table use 7 43
SQL Restore Script - Syntax Error 8 91
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
In this article I will describe the Backup & Restore 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.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

920 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

13 Experts available now in Live!

Get 1:1 Help Now