need help in query - 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 2005 Forums

Transact-SQL (2005)

need help in query

Author

Topic

krish001
Yak Posting Veteran

61 Posts

Posted - 2009-10-07 : 23:54:26

when SUM(v.[Cff Rating 1]) is NOT NULL and SUM(v.[Cff Rating 2]) is NOT NULL AND SUM(v.[Cff Rating 3]) is NOT NULL AND SUM(v.[Cff Rating 4]) is NOT NULL AND SUM(v.[Cff Rating 5]) is NULL then COUNT(v.[Cff Rating 1])+COUNT(v.[Cff Rating 2])+COUNT(v.[Cff Rating 3])+COUNT(v.[Cff Rating 4])
when SUM(v.[Cff Rating 1]) is NOT NULL and SUM(v.[Cff Rating 2]) is NOT NULL AND SUM(v.[Cff Rating 3]) is NOT NULL AND SUM(v.[Cff Rating 5]) is NOT NULL AND SUM(v.[Cff Rating 4]) is NULL then COUNT(v.[Cff Rating 1])+COUNT(v.[Cff Rating 2])+COUNT(v.[Cff Rating 3])+COUNT(v.[Cff Rating 5])
when SUM(v.[Cff Rating 1]) is NOT NULL and SUM(v.[Cff Rating 2]) is NOT NULL AND SUM(v.[Cff Rating 4]) is NOT NULL AND SUM(v.[Cff Rating 5]) is NOT NULL AND SUM(v.[Cff Rating 3]) is NULL then COUNT(v.[Cff Rating 1])+COUNT(v.[Cff Rating 2])+COUNT(v.[Cff Rating 4])+COUNT(v.[Cff Rating 5])
when SUM(v.[Cff Rating 1]) is NOT NULL and SUM(v.[Cff Rating 3]) is NOT NULL AND SUM(v.[Cff Rating 4]) is NOT NULL AND SUM(v.[Cff Rating 5]) is NOT NULL AND SUM(v.[Cff Rating 2]) is NULL then COUNT(v.[Cff Rating 1])+COUNT(v.[Cff Rating 3])+COUNT(v.[Cff Rating 4])+COUNT(v.[Cff Rating 5])
when SUM(v.[Cff Rating 2]) is NOT NULL and SUM(v.[Cff Rating 3]) is NOT NULL AND SUM(v.[Cff Rating 4]) is NOT NULL AND SUM(v.[Cff Rating 5]) is NOT NULL AND SUM(v.[Cff Rating 1]) is NULL then COUNT(v.[Cff Rating 2])+COUNT(v.[Cff Rating 3])+COUNT(v.[Cff Rating 4])+COUNT(v.[Cff Rating 5])
when SUM(v.[Cff Rating 1]) is NOT NULL and SUM(v.[Cff Rating 2]) is NOT NULL and SUM(v.[Cff Rating 3]) is NOT NULL and SUM(v.[Cff Rating 4]) is NOT NULL and SUM(v.[Cff Rating 5]) is NOT NULL then COUNT(v.[Cff Rating 1])+COUNT(v.[Cff Rating 2])+COUNT(v.[Cff Rating 3])+COUNT(v.[Cff Rating 4])+COUNT(v.[Cff Rating 5])
ELSE 0 end as Received

Iam using this to get the count based on cff ratings entered but it is working when all 5 cff ratings are entered in other case when they are nulls it is not workin plz help............

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-10-08 : 00:05:36

What's wrong with:

IsNull(Count(v.[Cff Rating 1],0) + 
IsNull(Count(v.[Cff Rating 2],0) +
IsNull(Count(v.[Cff Rating 3],0) + 
IsNull(Count(v.[Cff Rating 4],0) +
IsNull(Count(v.[Cff Rating 5],0) AS Received

It is pointless to perform SUMs just to determine if it's null, and even worse to then do a count on the same columns. IsNull or Coalesce will do the job.

krish001
Yak Posting Veteran

61 Posts

Posted - 2009-10-08 : 00:29:17

quote:
Originally posted by robvolk

What's wrong with:
IsNull(Count(v.[Cff Rating 1],0) +
IsNull(Count(v.[Cff Rating 2],0) +
IsNull(Count(v.[Cff Rating 3],0) +
IsNull(Count(v.[Cff Rating 4],0) +
IsNull(Count(v.[Cff Rating 5],0) AS Received
It is pointless to perform SUMs just to determine if it's null, and even worse to then do a count on the same columns. IsNull or Coalesce will do the job.

Iam getting error as count function require one argument

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-08 : 00:46:39

quote:
Originally posted by krish001

quote:
Originally posted by robvolk

What's wrong with:
IsNull(Count(v.[Cff Rating 1]),0) +
IsNull(Count(v.[Cff Rating 2]),0) +
IsNull(Count(v.[Cff Rating 3]),0) +
IsNull(Count(v.[Cff Rating 4]),0) +
IsNull(Count(v.[Cff Rating 5]),0) AS Received
It is pointless to perform SUMs just to determine if it's null, and even worse to then do a count on the same columns. IsNull or Coalesce will do the job.

Iam getting error as count function require one argument

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-10-08 : 01:06:36

Hi

Are you sure column name like this......

Cff Rating X

-------------------------
R...

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-10-08 : 01:15:37

quote:
Originally posted by senthil_nagore

quote:
Originally posted by krish001

quote:
Originally posted by robvolk

What's wrong with:
IsNull(Count(v.[Cff Rating 1]),0) +
IsNull(Count(v.[Cff Rating 2]),0) +
IsNull(Count(v.[Cff Rating 3]),0) +
IsNull(Count(v.[Cff Rating 4]),0) +
IsNull(Count(v.[Cff Rating 5]),0) AS Received
It is pointless to perform SUMs just to determine if it's null, and even worse to then do a count on the same columns. IsNull or Coalesce will do the job.

Iam getting error as count function require one argument

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Using COUNT function i think no need to use ISNULL...

Here the example...

CREATE TABLE #TEMP (ID INT , NAME VARCHAR(20))

INSERT INTO #TEMP VALUES(NULL,NULL)

SELECT * FROM #TEMP

SELECT COUNT(NAME) FROM #TEMP

SELECT ISNULL(COUNT(ID),0) FROM #TEMP

DROP TABLE #TEMP

-------------------------
R...

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-08 : 01:30:00

ya Count returns int (not null) value!

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources