Need some SQL code please :)

[Verngren]

Hi.

 

Could someone please help me in getting the following code to work:

 

SELECT "all address fields" TOP 500 SORT BY order amount FROM customers WHERE orderstatus = "Delivered"

 

I hope you can see what i want to do without being able to write proper SQL

 

Thanks!

 

// David

[NemoPS]

Hi,

 

 

uhm.. what do you mean by top 500? You only want 500 entries?

[Verngren]

Yes, thats correct.

 

I want the name and addresses of the people who made the 500 largest purchases.

[NemoPS]

SELECT ad.*, SUM(o.total_paid_real) as paid_by_customer

FROM ps_address ad

LEFT JOIN ps_orders o ON (o.id_customer = ad.id_customer)

LEFT JOIN ps_order_state os ON (os.id_order state = o.current_state)

WHERE os.name = "Delivered" AND os.id_lang = *enter the language ID here, how it's retrieved varies depending on the place you query from)

GROUP BY ad.id_customer

ORDER BY paid_by_customer

LIMIT 500

 

 

It *should* work but it's late and I'm pretty sleepy I haven't tested it either, let me know

[Verngren]

Right.. so heres what Ive got:

 

SELECT ad.*, SUM(o.total_paid_real) as paid_by_customer
FROM ps_address ad
LEFT JOIN ps_orders o ON (o.id_customer = ad.id_customer)
LEFT JOIN ps_order_state os ON (os.id_order_state = o.current_state)
WHERE os.name = "Delivered" AND os.id_lang = 7
GROUP BY ad.id_customer
ORDER BY paid_by_customer
LIMIT 500

 

The errors i get are:

os.name is not recognized.

Checked the table, and there is no column named "name"

 

I also couldnt locate anywhere in the order table that would define the order as delivered.

Not by ID nor name. Only history.. I dont know if that should be it.

[tomerg3]

Try this, replace xx with the status ID of "delivered"

SELECT a.*, SUM(o.total_paid_real) as total
FROM ps_orders o
LEFT JOIN ps_address a ON a.id_customer = o.id_customer
WHERE o.current_state = xx
GROUP BY a.id_address
ORDER BY total
LIMIT 500

[Verngren]

Hi tomerg3..

 

It gives me the following error:

- Unknown column 'o.current_state' in 'where clause'

[NemoPS]

This makes me wonder: which is your prestashop version?

[Verngren]

Prestashop version: 1.4.5.1

[NemoPS]

Oh well, of course it's not working then, try id_order_state instead of current_state

[Verngren]

SELECT a.*, SUM(o.total_paid_real) as total
FROM ps_orders o
LEFT JOIN ps_address a ON a.id_customer = o.id_customer
WHERE o.id_order_state = 5
GROUP BY a.id_address
ORDER BY total
LIMIT 500

 

#1054 - Unknown column 'o.id_order_state' in 'where clause'

[NemoPS]

Gosh, i looked for it but that was nowhere to bbe found :/ I fear they managed it through order history at this point :/

[musicmaster]

This should work:

 

SELECT ad.*, SUM(o.total_paid_real) as paid_by_customer FROM ps_address ad
LEFT JOIN ps_orders o ON (o.id_customer = ad.id_customer)
LEFT JOIN ps_order_history h ON (h.id_order = o.id_order)
WHERE h.id_order_state = '4'
GROUP BY ad.id_customer ORDER BY paid_by_customer DESC LIMIT 500

 

One thing to watch is whether you want to order state 4 (= sent) or 5(=delivered). Another things is that this doesn't check for the last status but just checks whether the order once had that status. So the order will still be counted if the products were sent back.