[Solved] Returning rows with 2+ attributes

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

SQL Server 2008 Forums

Transact-SQL (2008)

[Solved] Returning rows with 2+ attributes

Author

Topic

ravl13
Starting Member

38 Posts

Posted - 2015-03-17 : 11:50:39

Hello,

Let's say I have a table that has rows representing people:

SELECT Fname, Lastname, Attribute1, Attribute2, Attribute3, Attribute4, Attribute5
FROM People

The Attribute# columns are bit columns, with '0' indicating the person does not have that attribute, and '1' indicating that person does.

Now, I want to modify the above query to return only people who have any combination of two or more of those five attributes. So, anyone who only has one or zero total attributes with value of '1' in those five columns, would be excluded from the results.

How would you do that in Microsoft SQL Server?

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-17 : 12:28:31

SELECT Fname, Lastname, Attribute1, Attribute2, Attribute3, Attribute4, Attribute5
FROM People
where 0 = Attribute1 | Attribute2 | Attribute3 | ...

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-03-17 : 12:32:23

[code]With getcalc
As
(
SELECT [Fname],
[Lastname],
[countall] = Case When Attribute1 <> 0 Or Attribute2 <> 0 Or Attribute3 <> 0 Or Attribute4 <> 0 Or Attribute5 <> 0
Then Count(Attribute1)+Count(Attribute2)+Count(Attribute3)+Count(Attribute4)+Count(Attribute5)
End
FROM People
Group By [Lastname], [Fname]
)
Select [Lastname], [Fname],
Count([countall])
From getcalc
Where [countall] >= 2
Group By [Lastname], [Fname], [countall][/code]

We are the creators of our own reality!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-17 : 12:56:07

quote:
Originally posted by gbritton

SELECT Fname, Lastname, Attribute1, Attribute2, Attribute3, Attribute4, Attribute5
FROM People
where cast(Attribute1 as int) + cast(Attribute2 as int) + cast(Attribute3 as int) + cast(Attribute4 as int) + cast(Attribute5 as int) > 1
and

Changed to add

ravl13
Starting Member

38 Posts

Posted - 2015-03-17 : 16:52:49

sz1, thanks for your reply.

I'm having trouble with the

With getcalc As

portion of your post to work. I'm not familiar with that functionality, but after poking around a bit online trying to figure out the syntax, here's the errors I get when attempting to use it:

So I cannot get that pseudotable-thing "getcalc" to be recognized for the 2nd SELECT statement in your post. I didn't post my entire query in the screenshot, but I know there's not a stupid syntax error like missing a parentheses or something. Anyone have ideas on why I'm getting errors in the above screenshot?

I am connecting to a SQL 2008 instance (not SQL 2008 R2), if that matters.

***********

gbritton, thank you for your post as well. I actually found out that the attribute fields in question were single char fields (using 'y' and 'n') instead of bits, so I can't test out your example. I'm sure it would work fine though if I was working with bit columns, so I appreciate your time/suggestion as well.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-17 : 19:30:03

YOu can change the 'y' and 'n' to 1 and 0 like this:

...case attribute1 when 'y' then 1 else 0 end...

its a bit more typing but the result should work out

sz1
Aged Yak Warrior

555 Posts

Posted - 2015-03-18 : 06:34:59

Its not being recognised because you havent declared it as your case should be first and the select second.

It looks like your second select is upside down, you need

with getcalc As
(
Select FirstName, LastName...

The second select columns needs to be placed under the bottom select after a closing brackey )

We are the creators of our own reality!

ravl13
Starting Member

38 Posts

Posted - 2015-03-18 : 17:07:11

sz1,

I tried tackling the query again, and this time I'm not getting any errors on the "With getcalc As" part. But when I try to run the equivalent of this query from your original post:

SELECT    [Fname], 
		[Lastname],		  
		[countall] = Case When Attribute1 <> 0 Or Attribute2 <> 0 Or Attribute3 <> 0 Or Attribute4 <> 0 Or Attribute5 <> 0
		 Then Count(Attribute1)+Count(Attribute2)+Count(Attribute3)+Count(Attribute4)+Count(Attribute5)
		 End
FROM People	
Group By [Lastname], [Fname]

The query will not run. Essentially, the error message I get is this:


Msg 8120, Level 16, State 1, Line 1
Column 'People.Attribute1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 1
Column 'People.Attribute2' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
...
Msg 8120, Level 16, State 1, Line 2
Column 'People.Attribute5' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

So, I tried adding the attribute columns to the Group By clause. However, although the query runs, it doesn't return the desired output. Here's two queries I tested:

   --Query1
SELECT  [Fname], 
	[Lastname],		  
	[countall] = Case When Attribute1 = 'y' Or Attribute2 ='y' Or Attribute3 = 'y' Or Attribute4 = 'y' Or Attribute5 = 'y'
	Then Count(Attribute1)+Count(Attribute2)+Count(Attribute3)+Count(Attribute4)+Count(Attribute5)
	End
FROM People	
Group By [Lastname], [Fname], Attribute1, Attribute2, Attribute3, Attribute4, Attribute5

  --Query2
SELECT  [Fname], 
	[Lastname],		  
	[countall] = Case When Attribute1 = 'y' Or Attribute2 ='y' 
	Then Count(Attribute1)+Count(Attribute2)+Count(Attribute3)+Count(Attribute4)+Count(Attribute5)
	End
FROM People	
Group By [Lastname], [Fname], Attribute1, Attribute2

Both of these queries return similar counting behavior, different from what I actually want. If at least one of the attributes named in the countall/case statement is a 'Y', then the countall column always returns '5' (even in the 2nd query). If none of the attributes in the countall/case statement is a 'y', then the countall column always returns NULL. I never get a 1, 2, 3, or 4 result, even though I know I have columns that have those amount of attributes with a 'y' value. It seems that the number of "count" clauses I have is the number I will always get back in the countall column, if at least one column has a 'y' value. Can you think of a way to get it to actually return an accurate tally of number of Y's?

ravl13
Starting Member

38 Posts

Posted - 2015-03-18 : 17:26:21

gbritton, I gave your query another shot with your additional information as well. I see what you're trying to do, but it doesn't quite work. Here's my query (Activist, signedRecall, and SignedAffidavit are like Attribute1, Attribute2, Attribute3)


SELECT Firstname, Lastname, Address, Town, Zip, Precinct, Email, Phone, case Activist when 'y' then 1 else 0 end, case signedrecall when 'y' then 1 else 0 end, case SignedAffidavit when 'y' then 1 else 0 end
FROM Contacts
where cast(activist as int) + CAST(signedrecall as int) + CAST (signedaffidavit as int)
>0

I get this error message when trying to run it:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'Y' to data type int.

Which makes sense, because in my CAST clauses, I'm working with the original column, which is a char type and not an integer. However, when I try and rectify the problem like so:


SELECT Firstname, Lastname, Address, Town, Zip, Precinct, Email, Phone, 
case Activist when 'y' then 1 else 0 end as Activist2, 
case signedrecall when 'y' then 1 else 0 end as signedrecall2, 
case SignedAffidavit when 'y' then 1 else 0 end as signedaffidavit2
FROM Contacts
where cast(activist2 as int) + CAST(signedrecall2 as int) + CAST (signedaffidavit2 as int) > 0

I get the following error:

Msg 207, Level 16, State 1, Line 1
Invalid column name 'activist2'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'signedrecall2'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'signedaffidavit2'.

Any idea on how I should proceed? I need a way to work with the columns that use your case statements

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-18 : 17:35:33

where cast(cast activist when 'Y' then 1 else 0 end as int) + CAST(case signedrecall when 'Y' then 1 else 0 end as int) + CAST (case signedaffidavit when 'Y' then 1 else 0 end as int)

ravl13
Starting Member

38 Posts

Posted - 2015-03-19 : 15:43:20

quote:
Originally posted by gbritton

where cast(cast activist when 'Y' then 1 else 0 end as int) + CAST(case signedrecall when 'Y' then 1 else 0 end as int) + CAST (case signedaffidavit when 'Y' then 1 else 0 end as int)

Ah, thank you very much gbritton, that works! Marking thread as solved.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources