Home > Archive > PHP SQL > January 2005 > Trouble ordering dates from a query
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Trouble ordering dates from a query
|
|
| laredotornado@zipmail.com 2005-01-16, 3:56 pm |
| Hello, I am trying to summarize sales per month, and I want to order
the results by newest month to oldest month. Unfortunately, my query
so far is inadequate
$monthly_sale_query = "select
concat(concat(monthname(PURCHASE_DATE), ', '), year(purchase_date)) as
D, sum(total_sale) SALE FROM ORDERS group by D ORDER BY D DESC";
because it is ordering things according to the alphanumeric value of
"D", the concatenation of month and year. Does anyone know how to
achieve this in a single query? I am using PHP 4 with MySQL 4.
Thanks, - Dave
| |
|
|
Add an extra sorting cue to the group by/sort by column, and split it
in the client (also: use DATE_FORMAT() to ease the pain a bit)...
// "%Y%m|" gives YYYYMM with zero padding, plus a character to split on
// "%M, %Y" gives Mmmmm, YYYY
SELECT DATE_FORMAT( purchase_date, "%Y%m|%M, %Y" ) AS D,
SUM(total_sales) AS sales
FROM orders
GROUP BY D
ORDER BY D DESC
Column "D" will look something like...
'200411|November, 2005'
'200410|October, 2005'
'200409|September, 2005'
Then use your favourite PHP function to split the string on '|' to get
at the friendly title.
---
Steve
| |
|
|
> '200411|November, 2005'
> '200410|October, 2005'
> '200409|September, 2005'
<gah!>...or perhaps some other year like, oh, I don't know, 2004 maybe.
You get the idea...
---
Steve
|
|
|
|
|