Useful Database Queries.

[Guest Nicholas]

I'd like to be able to query the customer database (using phpmyadmin) to find customers who have spent over x amount and a separate query to find customers who have ordered more than once.

Can any one help?

Cheers

[Guest]

Well, finding customers who have ordered more than once is easy - just view customers in the admin panel and look at the number of orders column.

[burgensteen]

These a cool third part addition at cubecartforums.org you may want to consider (follow link in my signature)

[Guest Nicholas]

Mysty,

Thank you, I'd spotted that, but as I said above I want to do it through phpmyadmin.

I want to do it so I can select the best customers, export to csv and make a mailing lists dependent on the criteria mentioned above.

Burgensteen,

Thanks I'll take a look, but I don't really want to be adding mods atm as we'll soon be going on to CC4, If i had the SQL I could adapt it for both (if it needed to be adapted.

Cheers

[Guest Nicholas]

Okay, I've worked some out.

Find customers that have ordered above X amount or orders.

SELECT  * 

FROM  cubecart_customer 

WHERE  noOrders  >  'X'

Customers who's order was over X amount.

SELECT  * 

FROM cubecart_order_sum

LEFT  JOIN cubecart_customer ON cubecart_order_sum.customer_id = 

cubecart_customer.customer_id

WHERE prod_total >'X'

Customers who's order was over X amount and paid.

SELECT  * 

FROM cubecart_order_sum

LEFT  JOIN cubecart_customer ON cubecart_order_sum.customer_id = 

cubecart_customer.customer_id

WHERE prod_total > X AND 

STATUS  =3

Find the average order value

SELECT status, avg(prod_total)

FROM cubecart_order_sun group by status

If you can think of any more please add them to this thread.

Cheers,