1

How can I modify my query to return 0 if count(*) is NULL?

select monthname(timestamp), year(timestamp), count(*) 
from largehits 
where largeID = someid 
    and (month(timestamp)<=somemonth 
    and month(timestamp)>=somemonth 
    and year(timestamp)<=someyear 
    and year(timestamp)>=someyear ) 
group by month(timestamp), year(timestamp) 
order by timestamp asc

Thanks!

egrunin
  • 24,650
  • 8
  • 50
  • 93
Chris Mitchell
  • 11
  • 2
  • 6
    `COUNT()` can never return NULL. – BoltClock Dec 07 '10 at 17:48
  • Did you mean the other way around? Returning NULL when COUNT() returns 0? A simple CASE would do that for you. I don't know what the reason would be for doing that though. – manneorama Dec 07 '10 at 17:52
  • 1
    I think what you're more likely to hit is that the month/year combo doesn't exist so you get a missing row? – Basic Dec 07 '10 at 17:53
  • This query is to select hits from a range of dates . When there are no hits, it doesn't return a row for that month/year. Therefore, my resulting report doesn't have any results if there are no hits. – Chris Mitchell Dec 07 '10 at 17:54
  • Basiclife, you are right I believe. Looks like I need to create a for loop in the php code to parse the date span rather than rely on the mysql result. – Chris Mitchell Dec 07 '10 at 18:07
  • I think there might be some conflicting/confusing variable names in your proposed query... introduce max_month, min_month, max_year, min_year for clarification? – Riedsio Dec 07 '10 at 18:30

1 Answers1

2

BoltClock makes a good point in the comments that COUNT can never be NULL.

Generally you would just use COALESCE(expression,0) for this type of problem though.

Edit: Following clarification in the comments you would need to use an outer join on a numbers table to get the effect that you want.

Martin Smith
  • 438,706
  • 87
  • 741
  • 845