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.
| Author |
Topic |
|
gregd
Starting Member
2 Posts |
 Posted - 2012-04-27 : 12:13:36
|
| Hello,I am trying to get a query that will return a column location,and then there are 4 other columns that I want to look for a value and add them up. SoLocation Col1 Col2 Col3AAA zzz xxx zzzBBB yyy zzz xxxIn this case I would like to search for zzzMy output would be Location TotalAAA 2BBB 1I think I need something likeSELECT Location, COUNT(*) as "Total"then I am confused.thanks for any help |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-04-27 : 12:26:48
|
[code]select Location, Total = case when col1 = 'zzz' then 1 else 0 end + case when col2 = 'zzz' then 1 else 0 end + case when col3 = 'zzz' then 1 else 0 endfrom yourtable[/code]
KH[spoiler]Time is always against us[/spoiler] |
 |
|
|
gregd
Starting Member
2 Posts |
Posted - 2012-04-27 : 13:05:47
|
| Thank you,I can see how the query works.However I am getting an error#1054 - Unknown column 'Total' in 'field list' Total is not a column in the table. would I need a "as Total" somewhere?thanks |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-04-27 : 15:06:18
|
quote:
Originally posted by gregd
Thank you,I can see how the query works.However I am getting an error#1054 - Unknown column 'Total' in 'field list' Total is not a column in the table. would I need a "as Total" somewhere?thanks
where are you using this query?The error posted is not a standard error thrown by SQL Server------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
query to return sum of columns