MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:David Bouw Date:January 5 2000 2:56pm
Subject:Also question on Calculations with dates?
View as plain text  
Hallo Everyone
I would like any help with the following query..

I have got the follwing table:

perfect_customer_productorders:
|ordernr|quantity|date            |
|prod-1 |5         |01-03-2000|
|prod-2 |4         |01-05-2000|
|prod-1 |3         |01-03-2000|
|prod-2 |4         |30-12-1999|
|prod-3 |2         |01-03-2000|

I want to retrieve the total quantities of each product that has been sold
of the last 5 days (from 01-01-2000).

This is how far I came:
SELECT DISTINCTROW perfect_customer_productorders.ordernr,
Sum(perfect_customer_productorders.quantity) AS quantity FROM
perfect_customer_productorders GROUP BY
perfect_customer_productorders.ordernr;

This retrieves:
prod-1  : 8
prod-2  : 8
prod-3  : 2

This function works alright and calculates all records in the table, but I
only want the last five days...

I thought that maybe adding a 'where perfect_customer_productorders.date >=
"2000-1-1" to the function will do the job...

SELECT DISTINCTROW perfect_customer_productorders.ordernr,
Sum(perfect_customer_productorders.quantity) AS quantity FROM
perfect_customer_productorders where perfect_customer_productorders.date >=
"2000-1-1"
GROUP BY perfect_customer_productorders.ordernr;

This doesn't work, I still get the same result as above... Probably because
the database first makes the calculation and then selects the records which
are newer than 2000-01-01??

What am I missing here..

Thanks for any help..

Bye
David Bouw

Thread
Also question on Calculations with dates?David Bouw6 Jan
  • Re: Also question on Calculations with dates?sinisa6 Jan