COUNT

Question

COUNT

sharonsql2013

SSCertifiable

Points: 5908
More actions
January 8, 2014 at 9:56 am

#396073

This is what I need to actually count.
COUNT(*)round
from CTE1
where
( (Cte1.Roundtables = '1' OR Cte1.Roundtables= 'N/A')
AND
(CTE1.IsVideo = '1' OR CTE1.IsVideo = 'N/A'))
SO, I am trying to count the # of Roundtables which have either a number or 'N/A' ( mentioned in the CTE)
and the count of IsVideo if its a number OR ;N/A.
and then do an AND on both their respective counts.
Kindly advise

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 1

Let me mention that when I individually execute without the AND part for each , I get the count as 3 and 8 respectively .

I am expecting the answer to be 3 but the and brings 0

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

sharonsql2013 (1/8/2014)
This is what I need to actually count.
COUNT(*)round
from CTE1
where
( (Cte1.Roundtables = '1' OR Cte1.Roundtables= 'N/A')
AND
(CTE1.IsVideo = '1' OR CTE1.IsVideo = 'N/A'))
SO, I am trying to count the # of Roundtables which have either a number or 'N/A' ( mentioned in the CTE)
and the count of IsVideo if its a number OR ;N/A.
and then do an AND on both their respective counts.
Kindly advise

Pretty sparse on details here. Remember we can't see you screen. However I suspect you are getting 0 because your logic seems a little suspect to me. You must have a row that that RoundTables = 1 or N/A AND IsVideo = 1 or N/A.

This is the same thing as RoundTables in ('1', N/A') AND IsVideo in ('1', 'N/A'). I have a feeling that is not actually what you want though.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

sharonsql2013 SSCertifiable Points: 5908 More actions · Answer 3

Yes so I am counting RoundTables in ('1', N/A') AND IsVideo in ('1', 'N/A').

and then need to do an and between these to counts.

Should I separately count and then add ?

Sean Lange SSC Guru Points: 286590 More actions · Answer 4

sharonsql2013 (1/8/2014)
Yes so I am counting RoundTables in ('1', N/A') AND IsVideo in ('1', 'N/A').
and then need to do an and between these to counts.
Should I separately count and then add ?

Wouldn't changing the AND to OR solve the issue?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 5

Sean Lange (1/8/2014)
sharonsql2013 (1/8/2014)
Yes so I am counting RoundTables in ('1', N/A') AND IsVideo in ('1', 'N/A').
and then need to do an and between these to counts.
Should I separately count and then add ?
Wouldn't changing the AND to OR solve the issue?

I reckon Sean's nailed it here. Try running this, it will tell you:

SELECT

Roundtables,

IsVideo,

COUNT(*)

FROM CTE1

WHERE Cte1.Roundtables IN ('1', 'N/A')

OR IsVideo IN ('1', 'N/A')

GROUP BY Roundtables, IsVideo

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]