Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

How to find a Customer with Most Purchases


mirza_yasir4

Recommended Posts

I need to generate a report, which customer purchased most from the time shop is running. Then second most and then third most and so on.

 

I tried to locate a plugin but didn't get what I was looking for, so I thought to write a query which combine results from 2 tables

 

1. Orders

2. Orders_total

 

The customer data, like his name, his id and status of order (either it is delivered or rejected, 3 for delivered orders) is coming from first table

 

The amount of order is coming from second table.

 

First issue ,there are duplicate rows with same order id. I used the word DISTINCT in command but it is not working.

 

Second issue, I need to combine value of all unique orders, submitted by a single customer. here is the query and I am also attaching a picture that shows the results. Can anyone here help me to figure this out?

 

SELECT DISTINCT orders.orders_id, orders.customers_id, orders.customers_name, orders.orders_status, orders_total.value
FROM orders, orders_total
WHERE orders.orders_id = orders_total.orders_id
AND orders.orders_status =  '3'
ORDER BY orders_total.value DESC

post-203165-0-96023800-1348992245_thumb.gif

Thanks and Regards

Yasir Imran Mirza

My Contributions

Link to comment
Share on other sites

I tried the query like this, but not sure if result is correct.

 

SELECT orders.customers_id, orders.customers_name,
SUM(orders_total.value) as total
FROM orders, orders_total
WHERE orders.orders_id = orders_total.orders_id
AND orders.orders_status =  '3'
GROUP BY orders.customers_id
ORDER BY orders_total.value DESC

Thanks and Regards

Yasir Imran Mirza

My Contributions

Link to comment
Share on other sites

Almost, just the orders_total table contains different totals, if you look for the total of products excluding shipping fees use ot_subtotal, if you need to complete total, use ot_total, if you don't supply this, you are double counting

 

SELECT orders.customers_id, orders.customers_name,
SUM(orders_total.value) as total
FROM orders, orders_total
WHERE orders.orders_id = orders_total.orders_id
AND orders.orders_status =  '3'
AND orders_total.class = 'ot_subtotal'
GROUP BY orders.customers_id
ORDER BY orders_total.value DESC

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

One more question. Can I use Count function here?

Just to know that the Total Amount of customer's order is based on how many orders/transactions?

 

For instance, some customer might have made 5 purchases and his total order sum = 1000

But may be an other customer purchased only once but his total order sum = 1100

 

By having no. of successful orders he made we can say that customer trusted on us more than one time.

Thanks and Regards

Yasir Imran Mirza

My Contributions

Link to comment
Share on other sites

SELECT orders.customers_id, orders.customers_name,
SUM(orders_total.value) as total, count(*) as Nbr, avg(orders_total.value) as AvgOrder
FROM orders, orders_total
WHERE orders.orders_id = orders_total.orders_id
AND orders.orders_status = '3'
AND orders_total.class = 'ot_subtotal'
GROUP BY orders.customers_id
ORDER BY total DESC

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

I added select statements at the end

and changd the order by to the 'total' being the sum of the values

this way the order is on the total sum, not the biggest individual order amount

 

if you want to select the most frequent buyer, you can use

order by Nbr desc

 

HTH

KEEP CALM AND CARRY ON

I do not use the responsive bootstrap version since i coded my responsive version earlier, but i have bought every 28d of code package to support burts effort and keep this forum alive (albeit more like on life support).

So if you are still here ? What are you waiting for ?!

 

Find the most frequent unique errors to fix:

grep "PHP" php_error_log.txt | sed "s/^.* PHP/PHP/g" |grep "line" |sort | uniq -c | sort -r > counterrors.txt

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...