Adding Case Statements - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Adding Case Statements

Author

Topic

EvilTadpole
Starting Member

9 Posts

Posted - 2006-04-03 : 17:20:41

I have a table that includes four date fields. I'm trying to write a statement that will assign a 0 or 1 to each date field if a date is supplied (0 = no date, 1 = date), then I would like to add the the 0 and 1's togethor to determine for each set of items how many dates are supplied. Below is where I started. I need to take a count of rows, and group by typeid with only the total amount of dates supplied for each.

select count(itemno)Items, typeid,
case when date1 = '00000000' then 0 else 1 end
+ case when date2 = '00000000' then 0 else 1 end
+ case when date3 = '00000000' then 0 else 1 end
+ case when date4 = '00000000' then 0 else 1 end
from producttable
group by typeid

When I run this I get:
Server: Msg 8120, Level 16, State 1, Line 1
Column 'producttable.date1' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'producttable.date2' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'producttable.date3' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'producttable.date4' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I want the end result to look like this:

Items typeid (datecounts)
45632 1 0
3545 1 1
4558 1 2
64654 1 3
4654 1 4
46218 2 0
7834 2 1

etc etc.

Please help!!
Thanks
Erika

EvilTadpole
Starting Member

9 Posts

Posted - 2006-04-03 : 18:17:47

I figured it out, by putting the case statements in the group by clause too it was able to work... Yeah for me!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources