How to get toal of 2 counts?

Posted on 2011-05-04
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
    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.
    LVL 2

    Expert Comment

    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.

    LVL 1

    Expert Comment

    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
    LVL 9

    Accepted Solution

    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

    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?
    LVL 9

    Assisted Solution


    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

    Sorry - dbo.Biweekly says SQLServer
    LVL 21

    Expert Comment

    by:Alpesh Patel
    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

    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

    Thanks everyone.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This video discusses moving either the default database or any database to a new volume.

    779 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

    12 Experts available now in Live!

    Get 1:1 Help Now