Re: Strange query.
Could you expand a little on how that works? I am starting to think that I wont be able to get the results I need without post processing. Looking at the results: ... | 2007-01-09 20:02:15 | | 2007-01-09 20:02:15 | | 2007-01-09 20:03:20 | | 2007-01-09 20:08:33 | | 2007-01-09 20:08:33 | | 2007-01-09 20:12:19 | | 2007-01-09 20:18:59 | | 2007-01-09 20:23:03 | | 2007-01-09 20:50:11 | | 2007-01-09 20:50:11 | | 2007-01-09 20:50:12 | | 2007-01-09 20:50:12 | | 2007-01-09 20:50:12 | | 2007-01-09 20:50:12 | | 2007-01-10 01:16:09 | | 2007-01-10 01:18:31 | | 2007-01-10 03:00:22 | | 2007-01-10 03:09:23 | | 2007-01-10 03:12:23 | | 2007-01-10 03:15:23 | | 2007-01-10 03:18:23 | | 2007-01-10 03:30:24 | (the results are GMT, I need AST, hence the day change) All I am interested in is returning 24 values so with these results I would have something like: ... 20 - 14 21 - 0 22 - 0 23 - 0 0 - 0 1 - 2 2 - 0 3 - 6 Is this possible? On 10 Jan 2007 19:55:09 -, Felix Geerinckx [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] (Paul Halliday) wrote in news:[EMAIL PROTECTED]: I am trying to deal with empty values so that I can graph data over a 24 hour period without gaps. Have a look here: http://forums.mysql.com/read.php?10,133603,133607#msg-133607 -- felix -- 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]
Re: Strange query.
[EMAIL PROTECTED] (Paul Halliday) wrote in
news:[EMAIL PROTECTED]:
Could you expand a little on how that works?
USE test;
DROP TABLE IF EXISTS event;
CREATE TABLE event (timestamp DATETIME NOT NULL);
INSERT INTO event (timestamp) VALUES
('2007-01-09 20:02:15'),
('2007-01-09 20:02:15'),
('2007-01-09 20:03:20'),
('2007-01-09 20:08:33'),
('2007-01-09 20:08:33'),
('2007-01-09 20:12:19'),
('2007-01-09 20:18:59'),
('2007-01-09 20:23:03'),
('2007-01-09 20:50:11'),
('2007-01-09 20:50:11'),
('2007-01-09 20:50:12'),
('2007-01-09 20:50:12'),
('2007-01-09 20:50:12'),
('2007-01-09 20:50:12'),
('2007-01-10 01:16:09'),
('2007-01-10 01:18:31'),
('2007-01-10 03:00:22'),
('2007-01-10 03:09:23'),
('2007-01-10 03:12:23'),
('2007-01-10 03:15:23'),
('2007-01-10 03:18:23'),
('2007-01-10 03:30:24');
DROP TABLE IF EXISTS ints;
CREATE TABLE ints (i INT UNSIGNED NOT NULL PRIMARY KEY);
INSERT INTO ints (i) VALUES
( 0), ( 1), ( 2), ( 3), ( 4), ( 5), ( 6), ( 7), ( 8), ( 9),
(10), (11), (12), (13), (14), (15), (16), (17), (18), (19),
(20), (21), (22), (23), (24), (25), (26), (27), (28), (29);
SET @interval := 1*60*60; -- 1 hour in seconds
SET @min_dt := '2007-01-09 20:00:00';
SET @max_dt := '2007-01-10 19:59:59';
SELECT
@min_dt + INTERVAL ( i)[EMAIL PROTECTED] SECOND AS S,
@min_dt + INTERVAL (1+i)[EMAIL PROTECTED] - 1 SECOND AS E,
DATE_FORMAT(@min_dt + INTERVAL (i)[EMAIL PROTECTED] SECOND, '%H') AS H,
COUNT(timestamp)
FROM ints
LEFT JOIN event ON event.timestamp
BETWEEN @min_dt + INTERVAL ( i)[EMAIL PROTECTED] SECOND AND
@min_dt + INTERVAL (1+i)[EMAIL PROTECTED] - 1 SECOND
WHERE
@min_dt + INTERVAL ( i)[EMAIL PROTECTED] SECOND @max_dt
GROUP BY S, E
ORDER BY S;
--
felix
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange query.
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]
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]
Re: Strange query.
One of the most puzzling and challenging things to do with SQL can be
to show what's NOT there, as you're trying to do.
Many people opt to do such a report in their favorite scripting
language for this reason, as one can easily increment timestamps by a
given amount and re-do the query. Can be resource intensive to re-do
the queries for each hour or whatever, but it's often pretty easy.
Another option is to create a table used specifically for joining to
get units of time with no corresponding entries in the other table.
You could create a table like so:
CREATE TABLE all_hours (
date_hour DATETIME,
KEY (date_hour) );
then populate it like so, with perl:
$counter = 0;
while $counter 100 {
print INSERT INTO all_hours (date_hour) VALUES (
DATE_ADD(2000-01-01 00:00:00, INTERVAL $counter HOUR) );
$counter++;
}
Then you can join on that table.
A million hour entries would be good for 114 years or so. Fewer would
likely give somewhat better performance.
Dan
On 1/10/07, Paul Halliday [EMAIL PROTECTED] wrote:
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange query.
Ugh. My perl isn't quite right there. Here's a bit better (e.g.
working) example:
If you create the table, then put this in populate_hours.pl:
BEGIN
#!/usr/bin/perl
$counter = 0;
while ($counter 100) {
print INSERT INTO all_hours (date_hour) VALUES (
DATE_ADD('2000-01-01 00:00:00', INTERVAL $counter HOUR) );\n;
$counter++;
}
END
then run
./populate_hours.pl | mysql -h host -u user -ppassword -D database
you'll have a table full of hours.
Dan
On 1/10/07, Dan Buettner [EMAIL PROTECTED] wrote:
One of the most puzzling and challenging things to do with SQL can be
to show what's NOT there, as you're trying to do.
Many people opt to do such a report in their favorite scripting
language for this reason, as one can easily increment timestamps by a
given amount and re-do the query. Can be resource intensive to re-do
the queries for each hour or whatever, but it's often pretty easy.
Another option is to create a table used specifically for joining to
get units of time with no corresponding entries in the other table.
You could create a table like so:
CREATE TABLE all_hours (
date_hour DATETIME,
KEY (date_hour) );
then populate it like so, with perl:
$counter = 0;
while $counter 100 {
print INSERT INTO all_hours (date_hour) VALUES (
DATE_ADD(2000-01-01 00:00:00, INTERVAL $counter HOUR) );
$counter++;
}
Then you can join on that table.
A million hour entries would be good for 114 years or so. Fewer would
likely give somewhat better performance.
Dan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange query.
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.
Re: Strange query.
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
Re: Strange query.
[EMAIL PROTECTED] (Paul Halliday) wrote in news:[EMAIL PROTECTED]: I am trying to deal with empty values so that I can graph data over a 24 hour period without gaps. Have a look here: http://forums.mysql.com/read.php?10,133603,133607#msg-133607 -- felix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: strange query result
Your explain plan should not change based on the client you are using. The explain plan is the path for the query not the return of data to the client. Are you using sockets when connecting locally on the Free-BSD machine? Sockets are normally faster than using the tcp/ip port. -Original Message- From: Alex To: [EMAIL PROTECTED] Sent: 6/18/04 5:07 AM Subject: strange query result Hi, ppl! I have a MySQL-server on FreeBSD-5.2.1 computer and a client on another computer under windows xp. I have the following simple query SELECT internal_number,page_no FROM pages WHERE docs=307 ORDER BY page_no that results in 587 lines with two integers only. Simple query with low output volume The problem is that if I use a local client on FreeBSD-computer I get the result immediately. But if I try to do the same via tcp-connection it takes few minutes. After I used EXPAIN query it turned out that when I use tcp-connection USING FILE SORTING occurs. How to cure that? Alex -- 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]
Re: Strange query behavior
Linus Nikander wrote: Whilst trying to figure out why one of my queries wasn't working I ran into the following phenomenon which I'd be grateful if someone could explain. (I'm running mySQL 4.0.17 on a win2003 server, querying using the 0.9.4 mysql control center) The following query returns the expected result SELECT distinct(PO.error_type) AS error_type, COUNT(PO.error_type) AS occurrence FROM problematic_odds AS PO WHERE NOT (PO.error_type = 100) GROUP BY PO.error_type it returns 1008 ,212 for error_type and occurrence respectively. When I change the query to SELECT distinct(PO.error_type) AS error_type, COUNT(PO.error_type) AS occurrence FROM problematic_odds AS PO, active_odds AS AO This cartesian product will be empty if active_odds is empty. WHERE NOT (PO.error_type = 100) GROUP BY PO.error_type the query executes BUT doesn't return anything at all. I can't quite figure out why. The only difference between the two queries is that the second one has active_odds AS AO added to the FROM clause. Even though I don't use AO at all in the query the results still differ. Anyone have an explanation ? Having a superflous table in the WHERE clause shouldn't affect the query outcome should it ? //Linus Nikander - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange Query Results
On 14 Aug 2002, at 13:31, Sean C. Burbidge wrote: The query is: select t.lu_cn as cn, sum(if(strcmp(t.work_item_type, 'OPERATIONS'), t.reference_dollars_labor + t.reference_dollars_equip + t.reference_dollars_travel + t.reference_dollars_matl + t.reference_dollars_service, 0)) as ROPC, There you're summing the rows where t.work_item_type does not equal 'OPERATIONS'. Read the documentation for STRCMP(): http://www.mysql.com/doc/en/String_comparison_functions.html [snip] The problem I am encountering is that the figures for ROPC and NOPC *should* be in the RAMC and NAMC columns respectively, and vice-versa. If I run this query: select sum(t.reference_dollars_labor + t.reference_dollars_travel + t.reference_dollars_matl + t.reference_dollars_service + t.reference_dollars_equip) as ROPCfrom ii_tasks t where t.work_item_type = 'OPERATIONS' and t.lu_cn = '5016.003441' group by t.lu_cn; There you're summing the rows with t.work_item_type equals 'OPERATIONS'. It might be best to avoid STRCMP() entirely and just use =, which is generally clearer and less likely to lead to such errors. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Strange query issues
Are the first three machines closing their connections correctly? Duncan -- Duncan Salada | Titan | www.titan.com/testeval Email: [EMAIL PROTECTED] | Voice: 301-925-3222x375 | Fax: 301-925-3216 -Original Message- From: Gabriel [mailto:[EMAIL PROTECTED]] Sent: Monday, March 25, 2002 3:07 PM To: [EMAIL PROTECTED] Subject: Strange query issues Hello all. For my first post to the list, I have a very strange problem. (And yes, I have perused the archives. I haven't found anything similar.) Here's the situation: I've got 6 machines that that make requests to a 7th machine, which is running mysql-3.23.47. All the machines are FreeBSD 4.3-RELEASE. Our example query is SELECT id, username, domain FROM usermanagement LIMIT 1000, id is a primary key, and both username and domain are indexed. The first three servers make the query in about 0.13 seconds. The last three take about 6.01 seconds to perform the query. This has been consistant and repeatable. All the machines are running the same client software. They are all connected to the server over a switch, with no routers inbetween. They all are in the same subnet. They are also all configured in the same way - they are mail servers. The load is balanced, and no server is doing any significantly larger amount of processing than the others. Can anyone suggest anything for me to try to fix this problem? Many many thanks in advance, Gabriel. -- Gabriel Cain Unix Systems Administrator [EMAIL PROTECTED] Dialup USA, Inc. 888-460-2286 ext 208 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
