Re: MySQL query for average records per day
| From: | John Holmes | Date: | Thu, 16 Sep 2004 19:15:01 +0000 |
| Subject: | Re: MySQL query for average records per day | ||
| References: | 1 | Groups: | php.general |
| Request: | Send a blank email to php-general+get-196893@lists.php.net to get a copy of this message | ||
From: "Jeff Oien" <jeff@webdesigns1.com>
I have a database with a date field in this format 20041016 I'd like to count how many records were made on each day (except today) and average them. Can I do this in one query or will I need to do some more PHP stuff after I get some results? Thanks. Bare bones so far: $sql = "select date1 from $table_name where date1 != '$today'"; Jeff$sql = "SELECT date1, COUNT(date1) AS num_records FROM $table_name WHERE date1 != CURRENT_DATE GROUP BY date1"; You can't get the average and a count in the same query, so either calculate the average in PHP as you extract the results or run another query. $sql = "SELECT AVG(COUNT(date1)) AS avg_records FROM $table_name WHERE date1 != CURRENT_DATE GROUP BY date1"; Not sure if that second query is correct or not, though, since this is a PHP list. ---John Holmes...