Learn how to a build a cloud-first strategyRegister Now


How to get toal of 2 counts?

Posted on 2011-05-04
Medium Priority
Last Modified: 2012-06-21
I want to get the number to be 3 but with my query below I get 2.   How to fix this/ thank you

id             Monday Tuesday
123456      2800      0
123456      3000      3100
select (count(Monday) + count(Tuesday)) as totalDays
from dbo.Biweekly 
where StudyId=123456 and Monday<>0 and Tuesday<>0

Open in new window

Question by:lapucca
LVL 25

Expert Comment

by:Lee Savidge
ID: 35688184
You'll need to supply a bit more information on the data. What does 3 represent? Are you trying to get 3 rows back? 3 what? Give examples of the input data and examples of what you're trying to get.

Expert Comment

ID: 35688232
Check your data by

select * from dbo.Biweekly
where StudyId=123456 and Monday<>0 and Tuesday<>0

This will help you to understand the result. I assume, your where clause isn't sharp enough.


Expert Comment

ID: 35688237
Declare @Mycount int;
SET @Mycount = 0;
Select case when Monday <> 0 AND Tuesday <> 0 then @Mycount+2
                    when Monday <> 0 OR Tuesday <> 0 then @Mycount+1
           end as totalDays
From dbo.Biweekly
where StudyId=123456

Try this
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.


Accepted Solution

mayank_joshi earned 1000 total points
ID: 35688242
try this:-
select  (select (count(Monday)) as totalDays  
from dbo.Biweekly   
where StudyId=123456 and Monday<>0 ) +
(select (count(Tuesday)) as totalDays  
from dbo.Biweekly   
where StudyId=123456 and Tuesday<>0 )

Open in new window

LVL 12

Expert Comment

ID: 35688246
Your syntax for adding the counts together is correct.

However, your where clause is excluding the first row because Tuesday = 0.

I suspect you are trying to count the non-zero values.

What database are you using?

Assisted Solution

sarabhai earned 1000 total points
ID: 35688249

SELECT ( select (count(Monday))  as totalDays1 from dbo.Biweekly where  Id=123456 and Monday<>0)  
+( select (count(Tuesday))  as totalDays2 from dbo.Biweekly where  Id=123456 and Tuesday<>0)  

LVL 12

Expert Comment

ID: 35688250
Sorry - dbo.Biweekly says SQLServer
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35688784
its fine.

select COUNT(UnitsInStock) + COUNT(UnitsOnOrder)   from Products
where UnitsInStock <> 0 and UnitsOnOrder <> 0

select COUNT(UnitsInStock)    from Products
where UnitsInStock <> 0

select  COUNT(UnitsOnOrder)   from Products
where  UnitsOnOrder <> 0

Author Comment

ID: 35695686
I have table with different studyId entries for different weeks as entries.  So each row has a StudyID (which appears multiple rows for various week entries).  Each row as Monday - Sunday entry,  Users sometimes enter nothing or o for ceartain entry of that week.  I want to get a total count of ALL the days that the user enter with non-zero entry.  For exacmple, studyId 123456 has entered 8 weeks of data.  Out of those 3 rows of data (a row for each week), how many non-zero entries(Mon -Sun) did the user entered?    That's why I expected 3 as the answer for the example I show.
Saran and May, both your query seems to work by giving me 3 as answer.  Is there a way to do this for all 7 days' column's value?  I guess I could copy and do it for each of the day in a week.  Thanks.

Author Closing Comment

ID: 35695771
Thanks everyone.

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 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