Topic: Select seperate date ranges in one query

[xander85]

Hi All,

I have some MySQL experience, but I've been out of the "game" for a few years and I'm a bit rusty. I'm using the latest version of MySQL, but still not sure if I want I'm trying to do is possible:

I want to select and sum all rows for my "actual_sales" column for the last week and month in one query. Is this possible?

I'm currently using the following code to select the last week (ignore the date shift, my program starts it's week on Wed):

Code: [Select]

SELECT storeid AS store, (SELECT storenum FROM store WHERE storeid=store) AS storenum,
(SELECT name FROM store WHERE storeid=store) AS name, wedate, SUM(actual_sales) AS sales,
cust_count FROM `daily` WHERE wedate > '2010-03-09' AND wedate <= '2010-03-16' GROUP BY
storeid, wedate ORDER BY sales DESC
And the following to return the last month:

Code: [Select]

SELECT storeid AS store, (SELECT storenum FROM store WHERE storeid=store) AS storenum, (SELECT name FROM store
WHERE storeid=store) AS name, wedate, SUM(actual_sales) AS sales, cust_count FROM `daily`
WHERE month(wedate) = (MONTH(CURDATE()) - 1)  GROUP BY storeid ORDER BY sales DESC
These both work fine and return the sum of the "actual_sales" column for each "storeid", but I want to know if it's possible to put these two queries together?

I want to be able to display the last week's sales for each store (sorted by highest sum), then compare to the last month's sales for the same store, regardless of it being sorted (because one store could have had higher sales the previous month).

Thanks!

[fenway]

Well, you'd have to do the wedate grouping in code.