Oh, sorry. I set up a test table and then to send the query to the list, I changed the table names and column names to match yours...but I missed some. I think this one will work.
SELECT SUM(CASE when e.sid is null then 0 else 1 end) as counts, HOURS.hour FROM HOURS LEFT OUTER JOIN (SELECT 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 10:36 AM To "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> cc Subject Re: Strange query. e.c1? Giving me errors.. On 1/10/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > 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. > > > -- 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.
