See section 3.5.4 of the manual. The example there can be adapted to give
you
the date of the max or min disk size in one query. You have it right for
MAX
and MIN; there is also an AVG function.
>From the manual:
``Can it be done with a single query?''
Yes, but only by using a quite inefficient trick that I call the
``MAX-CONCAT trick'':
SELECT article,
SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer,
0.00+LEFT( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price
FROM shop
GROUP BY article;
----------
From: "Robert L. Yelvington" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Subject: need SQL assistance with a few selects
Date: Fri, 3 May 2002 09:24:02 -0500
Greetings folks,
I am able to do all this by pulling out data with PHP or PERL...just need
some straight MySQL command line advise!!!!
Specifically, I need some assistance with the following:
Here's my table's structure (table name is 'table'):
ID, CLIENT_NAME, CLIENT_LOGIN, DISK_SIZE, RUNTIME, RUNDATE
Here's example data:
"1","ABC INC","abcinc","1.2","00:30:00","2002-01-01"
"2","Joe's Restaurant","joesrest","0.2","00:30:00","2002-01-01"
"3","ABC INC","abcinc","3.2","00:30:00","2002-01-02"
"4","Joe's Restaurant","joesrest","2.2","00:30:00","2002-01-02"
"5","ABC INC","abcinc","1.0","00:30:00","2002-01-03"
"6","Joe's Restaurant","joesrest","1.2","00:30:00","2002-01-03"
This is what I'd like to find out:
1. An average disksize for each client, date doesn't matter.
The result I'd like to see would be something like:
"ABC INC","1.8"
"Joe's Restaurant","1.2"
2. The date of max disksize and the date of min disksize...in one query...if
that's possible.
I can get the min and max disksize as follows(please critique):
SELECT client_name, min(disk_size) from table group by client_name;
and likewise with the max() function.
Thanks so much!
Respectfully yours,
Rob
---------------------------------------------------------------------
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
