Hi all,
With the following SQL i'm getting the current result.
However, my 3 commented select statements I can't get to work.
I keep getting an error message as soon as I try to join the ps_order_detail table due to an aggregate function error.
I keep googling but haven't found a working solution in hours. If anyone experienced in SQL could help me i'd be so gratefull.
If you require more information please just ask.
I think mainly the issue is adding the ps_order_detail table is causing duplicate rows.
SELECT shop.customer_name as 'Customer Name', FORMAT(SUM(ROUND((orders.total_products+orders.total_shipping_tax_excl),2)),2,'de_DE') as 'Total Order', FORMAT(SUM(ROUND(orders.total_discounts/1.21,2)),2,'de_DE') as 'Total Voucher', -- FORMAT(SUM(IF((MAX(detail.product_price)+total_shipping_tax_excl)>total_discounts_tax_incl/1.21, ROUND(total_discounts_tax_incl/1.21,2), ROUND(MAX(detail.product_price)+total_shipping_tax_excl,2))),2,'de_DE') as 'HW S&S Only |', -- FORMAT(IF((MAX(product_price)+total_shipping_tax_excl)>total_discounts_tax_incl/1.21, ROUND((total_discounts_tax_incl/1.21)*0.99,2), ROUND((MAX(product_price)+total_shipping_tax_excl)*0.99,2)),2,'de_DE') as 'HW S&S Only (-1%) |' , -- FORMAT(ROUND(total_discounts/1.21,2) - IF((MAX(product_price)+total_shipping_tax_excl)>total_discounts_tax_incl/1.21, ROUND(total_discounts_tax_incl/1.21,2), ROUND(MAX(product_price)+total_shipping_tax_excl,2)),2,'de_DE') as "test", FORMAT(SUM(ROUND(orders.total_paid/1.21,2)),2,'de_DE') as 'xxx Payment' FROM storedb.ps_order_history history INNER JOIN storedb.ps_orders orders ON history.id_order=orders.id_order INNER JOIN storedb.ps_shop shop ON orders.id_shop=shop.id_shop -- INNER JOIN storedb.ps_order_detail detail -- ON orders.id_order=detail.id_order /* WHERE EXISTS (SELECT DISTINCT id_order FROM ps_order_detail HAVING FORMAT(SUM(IF((MAX(detail.product_price)+total_shipping_tax_excl)>total_discounts_tax_incl/1.21, ROUND(total_discounts_tax_incl/1.21,2), ROUND(MAX(detail.product_price)+total_shipping_tax_excl,2))),2,'de_DE') ) */ AND id_order_state = 4 AND orders.id_shop_group = 12 -- AND orders.id_shop = 41 AND history.date_add BETWEEN '2022-09-18' AND '2022-10-20' -- AND orders.id_order = 2156 GROUP BY customer_name HAVING SUM(FORMAT(ROUND((total_products+total_shipping_tax_excl),2),2,'de_DE')) > 1 ;
