Try something like this: SELECT SUM(CASE when e.c1 is null then 0 else 1 end) as counts, HOURS.hour FROM HOUR LEFT OUTER JOIN (SELET sid, date_format(timestamp, '%H')as hr FROM event) as e on HOURS.hour =e.hr WHERE timestamp between '2007-01-10 04:00:00' and '2007-01-11 04:00:00' AND sid=1 group by HOURS.hour
Donna "Paul Halliday" <[EMAIL PROTECTED]> 01/10/2007 09:48 AM To "Brent Baisley" <[EMAIL PROTECTED]> cc mysql@lists.mysql.com Subject Re: Strange query. That query doesn't return empty values. Just to clarify what I want as the result: My initial query was this, mysql> select count(*),DATE_FORMAT(timestamp,'%H') AS hour FROM event WHERE timestamp between '2007-01-10 04:00:00' and '2007-01-11 04:00:00' AND sid=1 GROUP BY hour; +----------+------+ | count(*) | hour | +----------+------+ | 4 | 04 | | 5 | 06 | | 5 | 07 | | 1 | 08 | | 7 | 09 | | 12 | 10 | | 73 | 12 | | 31 | 13 | | 50 | 14 | +----------+------+ 9 rows in set (0.03 sec) What I am looking for is 0's for every empty result and up to the end of the day. Thanks. On 1/10/07, Brent Baisley <[EMAIL PROTECTED]> wrote: > You can't join on the result of calculations in the field selection. The result is not associated with any table. So the problem > isn't so much with the date_format statement, but that you are joining on HOURS.hour the timestamp, not HOURS.hour the DATE_FORMAT. > I would think you would be getting an error when you run your SELECT. > Your group by can use the result of a calculation. So you may actually have two problems, since you are grouping on HOURS.hour, the > timestamp, the 'hour' the alias name for the calculation result. > I'm not sure why you don't just pull the hour from the timestamp either. > > SELECT COUNT(*), HOUR(timestamp) AS hour FOM HOURS > LEFT JOIN event ON HOURS.hour=HOUR(timestamp) > WHERE timestamp BETWEEN '2007-01-09 04:00:00' > AND '2007-01-10 04:00:00' AND sid=2 GROUP BY hour > > ----- Original Message ----- > From: "Paul Halliday" <[EMAIL PROTECTED]> > To: <mysql@lists.mysql.com> > Sent: Wednesday, January 10, 2007 8:39 AM > Subject: Strange query. > > > > Hi, > > > > I am trying to deal with empty values so that I can graph data over a > > 24 hour period without gaps. > > > > I created a table called HOURS which simply has 0->23 and I am trying > > to do a join on this to produce the desired results. I think that the > > DATE_FORMAT in the query is screwing things up. > > > > The query looks something like this: > > > > SELECT COUNT(*),DATE_FORMAT(timestamp,'%H') AS hour FROM HOURS LEFT > > JOIN event ON HOURS.hour=hour WHERE timestamp BETWEEN '2007-01-09 > > 04:00:00' AND '2007-01-10 04:00:00' AND sid=2 GROUP BY HOURS.hour; > > > > Any help would be appreciated. > > > > Thanks. > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare.
