May 19, 2010 at 2:30 pm
See Pic: http://tareemnet.com/pic/untitled.png
See the result in the pic, it is not null and not a number. When I sum the result to another number, it results confusion. I need to get this problem out, since the null result could be solved using isnull(col,0), But how this is solved.
I want to perform this query inside a procedure:
set @res = 1+ (select sum(CPC) from campaignclicks group by CampaignId having campaignid=8)
Thank:-)
If more clarification required, please ask me.
May 19, 2010 at 2:37 pm
Please provide the table definition for campaignclicks
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 19, 2010 at 2:56 pm
The image is the return of zero rows. "Having" gets applied after the group by (and therefore after the aggregation), so I am going to guess your table has zero records for campaignid = 8, so that's why you have zero rows.
A where clause is applied before the aggregation, so sum (no rows) returns a row with a null value. In your case, the query sums by campaign, then asks for the row having a campaignid that I am guessing doesn't exist, thus (correctly) returns no rows.
You should use Having generally to filter on the result of the aggregate function (such as "having sum(cpc)>100"), not for filtering the underlying rows themselves.
May 19, 2010 at 3:03 pm
hey guys, please do not go away from what i need.
What i need is that we can solve the problem of a null value in summation by using
isnull(comm,0)
but here how we can solve the problem of returning no value, how we can convert that to a number '0' to be then treated in summation??
May 19, 2010 at 3:09 pm
sum(isnull(CPC,0))
But is that all you are looking for?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 20, 2010 at 12:49 am
Why dont you write the query as;
Select Sum(CPC)
from campaignclicks
where campaignID = 8
May 20, 2010 at 10:14 am
Atif Sheikh (5/20/2010)
Why dont you write the query as;
Select Sum(CPC)
from campaignclicks
where campaignID = 8
Throw in the isnull and it seems this would work as well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 20, 2010 at 10:23 pm
CirquedeSQLeil (5/20/2010)
Atif Sheikh (5/20/2010)
Why dont you write the query as;
Select Sum(CPC)
from campaignclicks
where campaignID = 8
Throw in the isnull and it seems this would work as well.
Very right. But I need to ask one thing here. Why not to use Default Value 0 for columns like CPC. I think its better approach. Use of ISNULL for CPC like columns can give you alot of problems if someone (like me) forgets to use it in the query...:-D
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply