0

I have some statistics I need to report on a monthly basis. I need my query to return 0's for statistics which aren't there. The only way I know to do this is to use SQL to generate a list of days within the specified time period and LEFT JOIN the statistic data that I need to report on.

Problem is.... I've never not selected from a table like that... how do I do that?

kapa
  • 77,694
  • 21
  • 158
  • 175
Ben
  • 60,438
  • 111
  • 314
  • 488
  • 3
    Similar to http://stackoverflow.com/questions/2693320/sql-date-rows-for-inactivity-days, http://stackoverflow.com/questions/75752/what-is-the-most-straightforward-way-to-pad-empty-dates-in-sql-results-on-either, http://stackoverflow.com/questions/2670183/displaying-zero-valued-months-with-sql, etc... – Mark Byers Apr 22 '10 at 20:43

3 Answers3

0

Compare your returned values, for statistic that never existed your returned value probably will be null, all you need to compare returned value with null and if null return 0 otherwise return real result 

select s.Name , IFNULL(d.Value, 0) as Value
 from Statustic s
LEFT JOIN t_Data d on (d.Stat_ID=s.ID)

and you can use this link to read more at http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html#function_isnull

Senad Meškin
  • 13,597
  • 4
  • 37
  • 55
0

If the values are NULL use 

SELECT IFNULL( statistic, 0 ) AS statistic FROM myTable;
Ben
  • 16,275
  • 9
  • 45
  • 63
0

I ended up just using SQL to pull the data I have for the given period and handling everything else on the PHP side. Not exactly what I wanted, but it works.

Ben
  • 60,438
  • 111
  • 314
  • 488