SQL Count Problems... I think.....mind stumper

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

SQL Server Development (2000)

SQL Count Problems... I think.....mind stumper

Author

Topic

jmiller
Starting Member

7 Posts

Posted - 2005-06-28 : 11:53:18

Here is my code; I am getting and error when I try to count how many records are in the statement. What I am trying to do is a comparison of freight from year to year. Everything worked great until I added the Pro2003 - Pro2005 with the count. Below is my code, and above that is my error message. All I want to do is count how many record there are.

ERROR
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value '00006R3W80' to a column of data type int.

CODE BELOW

Select
	id,
	Customer_name,
	Cost2003,
	Pcs2003,
	Wgt2003,
	Pro2003,
	Cost2004,
	Pcs2004,
	Wgt2004,
	Pro2003,
	Cost2005,
	Pcs2005,
	Wgt2005,
	Pro2005
From
	(
	Select 
		Id, 
		Customer_name,
		Cost2003 = sum(case when year([PRO DATE])=2003 then AMOUNT else 0 end),
		Pcs2003 = sum(case when year([PRO DATE])=2003 then PCS else 0 end),
		Wgt2003 = sum(case when year([PRO DATE])=2003 then WEIGHT else 0 end),
		Pro2003 = count(case when year([PRO DATE])=2003 then PRONUM else 0 end),
		Cost2004 = sum(case when year([PRO DATE])=2004 then AMOUNT else 0 end),
		Pcs2004 = sum(case when year([PRO DATE])=2004 then PCS else 0 end),
		Wgt2004 = sum(case when year([PRO DATE])=2004 then WEIGHT else 0 end),
		Pro2004 = count(case when year([PRO DATE])=2004 then PRONUM else 0 end),
		Cost2005 = sum(case when year([PRO DATE])=2005 then AMOUNT else 0 end),
		Pcs2005 = sum(case when year([PRO DATE])=2005 then PCS else 0 end),
		Wgt2005 = sum(case when year([PRO DATE])=2005 then WEIGHT else 0 end),
		Pro2005 = count(case when year([PRO DATE])=2005 then PRONUM else 0 end)
		
	From PRODETAIL
	Where [group] = 'STONERIDGE'
	Group by id, customer_name
	) A

Thank for all the help in advance.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-28 : 13:01:57

why are you counting a case when??

Amount or PCS contains non numeric values.... what do you want to do with them.

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

jmiller
Starting Member

7 Posts

Posted - 2005-06-28 : 13:24:55

AMOUMT = Currency
PCS = Number
WEIGHT = Number
Pro = Text

Thanks for the assistance

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-28 : 13:53:07

yeah... what is this supposed to do??
count(case when year([PRO DATE])=2005 then PRONUM else 0 end)

are you trying to count all of the 2005 dates? or sum all of the pronums where the date is 2005?

count(*) basically counts the non-null entries. so a case with no null involved is fruitless.

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

jmiller
Starting Member

7 Posts

Posted - 2005-06-28 : 14:11:15

Yes all of the 2005 dates. I am trying to do a year to year comparison.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-06-28 : 14:39:11

a count of the 2005 dates:
sum(case when year([Pro Date])=2005 then 1 else 0 end)

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources