Hi all,

I've been trying to set up an excel document which will show the sales of a single item only (ie not the complete history of all shop sales). The way I've done this so far is via this query:


SELECT d.order_id as `Order Number`, FROM_UNIXTIME(a.cdate) AS `Order Date`, b.order_status_name AS `Order Status`, a.order_total as `Total`, a.order_subtotal as `Subtotal`, a.order_tax as `Tax`, c.user_email as `Bill To Email`, c.title as `Bill To Title`, c.first_name as `Bill To First Name`, c.middle_name AS `Bill To Middle Name`, c.last_name as `Bill To Last Name`, c.address_1 AS `Bill To Address 1`, c.address_2 AS `Bill To Address 2`, c.city AS `Bill To City`, c.zip AS `Bill To Zip/Postal Code`, d.product_attribute 'Customer Notes', a.customer_note as `Secondary Customer Note`
FROM pku_vm_orders a,
pku_vm_order_status b,
pku_vm_order_user_info c,
pku_vm_order_item d,
( SELECT sh.*
FROM pku_vm_order_user_info sh
WHERE order_info_id = ( SELECT MAX(order_info_id)
FROM pku_vm_order_user_info shmax
WHERE shmax.order_id = sh.order_id )
) AS ship
WHERE a.order_status = b.order_status_code
AND d.order_item_sku='WAMSS.6th.JumperNM'
AND a.user_info_id = d.user_info_id
AND c.address_type = 'BT'
AND a.order_id=c.order_id
AND ship.order_id = c.order_id
ORDER BY 'Bill To Last Name' ASC



And I would change 'WAMSS.6th.JumperNM' for the SKU of each respective item. It works, however the output is often unreliable as there seems to be some mixing up of the transaction of interest and a prior transaction the person had with a different product. It seems to occur when the 'user_id' is carried over between transactions but I'm not sure how to fix it

Could anyone post the SQL query that they use? Or point out to me why two different transactions are getting mixed up?

Found a partial solution!

The following code is a lot neater and works better:

SELECT d.order_id as `Order Number`, FROM_UNIXTIME(a.cdate) AS `Order Date`, b.order_status_name AS `Order Status`, c.title as `Bill To Title`, c.first_name as 'First Name', c.last_name as 'Last Name', c.user_email as `Bill To Email`, c.address_1 AS `Bill To Address 1`, c.address_2 AS `Bill To Address 2`, c.city AS `Bill To City`, c.zip AS `Bill To Zip/Postal Code`, d.product_attribute 'Customer Notes', a.customer_note as `Secondary Customer Note`
FROM pku_vm_orders a,
pku_vm_order_status b,
pku_vm_order_user_info c,
pku_vm_order_item d
WHERE a.order_status = b.order_status_code
AND d.order_id = c.order_id
AND a.order_id=c.order_id
AND d.order_item_sku='WAMSS.6th.JumperM'
ORDER BY d.order_id ASC


I found the problem - duplicate entries seem to appear for some reason when someone enters a different shipping address to their home address. I think I'll have to remove that option from the screen unless anyone has any other ideas. Thanks anyway, hopefully someone can use the sql query i created for their project

Thanks for sharing your findings! Hope it helps someone else!

-Mats