Returning 0 for count(*) value if count is null? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

Returning 0 for count(*) value if count is null?

Author

Topic

crugerenator
Posting Yak Master

126 Posts

Posted - 2010-08-31 : 14:25:46

I've got a query that counts the results of a subselect. It works great when my subselect returns rows, but if my subselect returns nothing, the count portion doesn't return anything. I'm trying to get it to still return what I need, just with 0's populating the output instead of whatever the count would be. I know the problem is that the count portion has nothing to select from when the subquery returns nothing, so I think I just need the subquery to return populate relcust regardless of if it doesn't fund any results.

Here's my query:



SELECT Coalesce(COUNT(*), '0')         AS prodcount, 
       relcust, 
       Datename(MONTH, transdate) AS MONTH, 
       Datepart(MONTH, transdate) AS monthnum, 
       Datename(YEAR, transdate)  AS YEAR 
FROM   (a long subselect that has the possibility of returning 0 rows. when it returns rows, it returns relcust, transdate, and a field that tracks each transaction (this is what the count is done on)) AS tmp 
GROUP  BY relcust, 
          Datename(MONTH, transdate), 
          Datepart(MONTH, transdate), 
          Datename(YEAR, transdate) 
ORDER  BY relcust, 
          YEAR DESC, 
          monthnum DESC

output results if the subquery does return results:


prodcount    relcust     month    monthnum     year
    1         pers1       aug        8           2010
    2         pers2       aug        8           2010
    3         pers3       aug        8           2010

these would be the results of the subquery:


transname    relcust    transdate
   abc        pers1        8/6/2010
   abc1       pers2        8/1/2010
   abc2       pers2        8/5/2010
   def1       pers3        8/10/2010
   def2       pers3        8/15/2010
   def3       pers3        8/20/2010

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-08-31 : 19:58:24

SELECT isnull(COUNT(tmp.and a field that tracks each transaction (this is what the count is done on), 0) AS prodcount, ?

Jim

Everyday I learn something that somebody else already knew

crugerenator
Posting Yak Master

126 Posts

Posted - 2010-09-01 : 10:19:14

eh, I think this is actually impossible as is. I'll have to figure out new query logic to get this to work.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-09-01 : 11:44:20

[code]
SELECT Coalesce(COUNT(*), '0') AS prodcount,
relcust,
Datename(MONTH, transdate) AS MONTH,
Datepart(MONTH, transdate) AS monthnum,
Datename(YEAR, transdate) AS YEAR
FROM yourTable t
LEFT JOIN (a long subselect that has the possibility of returning 0 rows. when it returns rows, it returns relcust, transdate, and a field that tracks each transaction (this is what the count is done on)) AS tmp
on t.something = tmp.something
GROUP BY relcust,
Datename(MONTH, transdate),
Datepart(MONTH, transdate),
Datename(YEAR, transdate)
ORDER BY relcust,
YEAR DESC,
monthnum DESC[/code]
you'll need to add alias prefixes, but this is basically the way to do it. in other words, run a query that is guaranteed to return the records you need, then OUTER JOIN to the query that may not return results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources