How to get toal of 2 counts?

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

lapuccaAsked:
Who is Participating?
 
mayank_joshiCommented:
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


0
 
Lee SavidgeCommented:
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.
0
 
GnarfCommented:
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.

HTH
Gnarf
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
psvineeshCommented:
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
Thanks
0
 
Paul_Harris_FusionCommented:
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?
0
 
sarabhaiCommented:

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)  

0
 
Paul_Harris_FusionCommented:
Sorry - dbo.Biweekly says SQLServer
0
 
Alpesh PatelAssistant ConsultantCommented:
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
0
 
lapuccaAuthor Commented:
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.
0
 
lapuccaAuthor Commented:
Thanks everyone.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.