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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-07-06 : 07:45:55
|
| Fred writes "I have a table (tblAgents) that contains customer info, and one of the fields in that table is DailyLimit - a numeric value that controls the dollar amount we can bill them on a daily basis.In another table (tblSendLog), a record is created every time we bill something to a client. When this happens, the field called BilledRate is poplated with the amount they were billed.I have a query I use that returns the amount a client has already been billed today and the "available balance" - the amount that we can still bill them during the current day.The query is below, the problem I have is that when nothing has been billed to a client during the specific day, the query returns no rows. Presumably because the where clause has nothing to match in tblSendLog. Is there any way I could make Sum(BilledRate) return 0 instead of nothing if no rows match the query? I tried using coalesce, but it didn't work because the query is returning nothing, not null.Any help would be greatly appreciated.SELECT Sum(BilledRate) AS TotalBilled, DailyLimit-Sum(BilledRate) AS AvailableBalance FROM tblSendLog LINNER JOIN tblAgents A ON A.AgentID=L.AgentID WHERE L.AgentID=1379 AND DateDiff(d,datetime,getDate())=0GROUP BY DailyLimitP.S. I'm not really sure why I need the group by at the end, it complained about it so I tacked it on. Don't laugh at me, I'm not even a programmer." |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2005-07-06 : 08:07:23
|
| Try to use Sum(coalesce (column,0))MadhivananFailing to plan is Planning to fail |
 |
|
|
DonAtWork
Master Smack Fu Yak Hacker
2167 Posts |
Posted - 2005-07-06 : 09:47:59
|
or Sum(ISNULL (column,0)) , which i would prefer, since there are only 2 choices. COALESCE is for when you have a list of 3 or more choices. (yea BOL says 2, but why use the overhead if it is only 2 when you have ISNULL.  It complained about the GROUP BY because you have an aggregate function (SUM) in your SELECT.*need more coffee*SELECT * FROM Users WHERE CLUE > 0(0 row(s) affected) |
|
|
|
|
|
|
|
|
SUM returns nothing instead of null