Learn how to a build a cloud-first strategyRegister Now

x
Solved

# How to get toal of 2 counts?

Posted on 2011-05-04
Medium Priority
293 Views
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
``````
0
Question by:lapucca

LVL 25

Expert Comment

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.
0

LVL 2

Expert Comment

ID: 35688232

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

HTH
Gnarf
0

LVL 1

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
Thanks
0

LVL 9

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 )
``````

0

LVL 12

Expert Comment

ID: 35688246

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

LVL 9

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)

0

LVL 12

Expert Comment

ID: 35688250
Sorry - dbo.Biweekly says SQLServer
0

LVL 21

Expert Comment

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
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.
0

Author Closing Comment

ID: 35695771
Thanks everyone.
0

## Featured Post

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ā¦
###### Suggested Courses
Course of the Month21 days, 5 hours left to enroll