MySQL

mysql logic

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Sep 2008

Posts: 2

Reputation: johnwayne77 is an unknown quantity at this point

Solved Threads: 0

johnwayne77 johnwayne77 is offline

Offline

Newbie Poster

mysql logic

Dec 24th, 2008

here is my situation:

i have a prepay online store and i advertised a contest to win a free code when buying at least 5 codes during 24.11-24.12 2008 period.

now this is my table:

Help with Code Tags

MySQL Syntax (Toggle Plain Text)

orders_id 	customers_id 	customers_name 	customers_company 	customers_street_address 	customers_suburb 	customers_city 	customers_postcode 	customers_state 	customers_country 	customers_telephone 	customers_email_address 	customers_address_format_id 	delivery_name 	delivery_company 	delivery_street_address 	delivery_suburb 	delivery_city 	delivery_postcode 	delivery_state 	delivery_country 	delivery_address_format_id 	billing_name 	billing_company 	billing_street_address 	billing_suburb 	billing_city 	billing_postcode 	billing_state 	billing_country 	billing_address_format_id 	payment_method 	cc_type 	cc_owner 	cc_number 	cc_expires 	last_modified 	date_purchased 	orders_status 	orders_date_finished 	currency 	currency_value

i don't have a sql logic to : select all orders depending on date_purchased field which is in '2008-11-21 21:47:45' format, then calculate total number of orders for each of the customers in that period, depending on customers_id and orders_id i guess, then make a list of the customers that have the number of orders above or equal to 5 and finally randomly select one.

any ideas?

•

Join Date: Aug 2007

Posts: 165

Reputation: Fest3er is an unknown quantity at this point

Solved Threads: 18

Fest3er

Offline

Junior Poster

Re: mysql logic

Dec 25th, 2008

•

Originally Posted by johnwayne77

... i have a prepay online store and i advertised a contest to win a free code when buying at least 5 codes during 24.11-24.12 2008 period. ... any ideas?

This is pseudo-codeish so you have to RTFM and google to make it work.

You need to use count() and group by. Something like this is kind-of close:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
SELECT count(id), id FROM table WHERE count(id)>4 AND (date_purchased>='2008-11-24' AND  date_purchased<="2008-12-24") GROUP BY id ORDER BY count(id);
select count(id), id from table where count(id)>4 and (date_purchased>='2008-11-24' and  date_purchased<="2008-12-24") group by id order by count(id);