COUNTIF in SQL 2008 R2 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

COUNTIF in SQL 2008 R2

Author

Topic

sql_dan
Starting Member

43 Posts

Posted - 2014-08-08 : 05:46:14

Ok firstly my apologies for this, I have searched for the answer but can't see anything relevant.

I have a query with the following data set:

Person Seen 1 Seen 2 Order
72 Red Square 1
72 Red Square 2
140 Blue Circle 1
631 Green Rect 1
631 Green Flat 2
631 Green Round 3
631 Green Sphere 4
2753 Pink Circle 1
2753 Orange Flat 2
2753 Orange Bleep 3
2753 Orange Circle 4

What i'm trying to achieve is that if in the Seen 2 column any of the following are seen:

- Beep
- Round

Then mark all of the records for the person with a 1 so it looks like this:

Person Seen 1 Seen 2 Order COUNTIF
72 Red Square 1 0
72 Red Square 2 0
140 Blue Circle 1 1
631 Green Rect 1 0
631 Green Flat 2 0
631 Green Round 3 0
631 Green Sphere 4 0
2753 Pink Circle 1 1
2753 Orange Flat 2 1
2753 Orange Bleep 3 1
2753 Orange Circle 4 1

Any help or guidance would be brilliant. I just cant get my head around it!

Thanks in advance
Dan

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-08 : 06:13:35

why is the countif value 1 for person 140?seen 2 is not either Beep or Round

Javeed Ahmed

sql_dan
Starting Member

43 Posts

Posted - 2014-08-08 : 07:30:39

Hi Javeed,

That is what I am trying to code. If any of the Seen 2 codes I am looking for (Beep, Round) happened for a person, mark all records for that person with a 1, not just the instances of where they happened.

Sorry for any confusion.

quote:
Originally posted by ahmeds08

why is the countif value 1 for person 140?seen 2 is not either Beep or Round

Javeed Ahmed

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-08 : 07:52:20

You really haven't cleared things up. Neither person you have mentioned have either of those two codes. The closest is 2753 who has bLeep not beep.

140 Blue Circle 1 1

2753 Pink Circle 1 1
2753 Orange Flat 2 1
2753 Orange Bleep 3 1
2753 Orange Circle 4 1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-08-08 : 08:15:02

MAX(CASE WHEN [Seen 2] IN ('Beep', 'Round') THEN 1 ELSE 0 END) OVER (PARTITION BY Person)

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

sql_dan
Starting Member

43 Posts

Posted - 2014-08-08 : 08:32:27

YES! Thank you SwePese
Exactly what I was after!

quote:
Originally posted by SwePeso

MAX(CASE WHEN [Seen 2] IN ('Beep', 'Round') THEN 1 ELSE 0 END) OVER (PARTITION BY Person)

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

MichaelJSQL
Constraint Violating Yak Guru

252 Posts

Posted - 2014-08-08 : 08:58:11

yeah - but you did not get the result set you were asking for: 140 is still 0 : I think your result set was wrong in your example.

Person Seen1 Seen2 SomeOrder (No column name)
72 Red Square 1 0
72 Red Square 2 0
140 Blue Circle 1 0
631 Green Rect 1 1
631 Green Flat 2 1
631 Green Round 3 1
631 Green Sphere 4 1
2753 Pink Circle 1 0
2753 Orange Flat 2 0
2753 Orange Bleep 3 0
2753 Orange Circle 4 0

sql_dan
Starting Member

43 Posts

Posted - 2014-08-08 : 09:10:12

Hi MichaelJSQL

Indeed the example was wrong - the code is working exactly as intended now!

Thanks very much for your help.
Sorry for any confusion!

__________________________________________________
If you cant sleep at night, its not the coffee its the bunk!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources