[SOLVED] SQL Reporting error (Aggregate function, group by, duplicate records)

[Nickovitshj]

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
;

 

Edited October 20, 2022 by Nickovitshj (see edit history)

[Nickovitshj]

Hi all,

Managed to get in touch with a good mate who have good SQL knowledge.
Basically we rewrote everything from start to figure out the exact issue. Seems like we needed a subQuery to achieve our result and avoid duplicate records.

Please keep in mind that this is a query written for a multistore setup.
Though, you can probably repurpose it for single store.

Final SQL:

SELECT 
customer_name as 'xxx Customer' ,
FORMAT(SUM(ROUND((o.total_products+o.total_shipping_tax_excl),2)),2,'de_DE') as 'Total Order',
FORMAT(SUM(ROUND(o.total_discounts/1.21,2)),2,'de_DE') as 'Total Voucher',
FORMAT(SUM(productPrice),2,'de-DE') as 'HW S&S Only | ',
FORMAT(SUM((productPrice)*0.99),2,'de-DE') as 'HW S&S Only (-1%) | ',
FORMAT(SUM(ROUND(o.total_discounts/1.21,2) - IF(productPrice+total_shipping_tax_excl>total_discounts_tax_incl/1.21, ROUND(total_discounts_tax_incl/1.21,2), ROUND(productPrice+total_shipping_tax_excl,2))),2,'de_DE') as "Company | xxx CRM",
FORMAT(SUM(ROUND(o.total_paid/1.21,2)),2,'de_DE') as 'xxx Payment'
FROM ps_orders o
INNER JOIN (SELECT id_order, MAX(product_price) as productPrice FROM ps_order_detail GROUP BY id_order) detail
ON o.id_order=detail.id_order
INNER JOIN ps_shop shop
ON o.id_shop=shop.id_shop
INNER JOIN storedb.ps_order_history history
ON o.id_order=history.id_order
WHERE o.id_shop_group = 12
AND id_order_state = 4 
AND history.date_add BETWEEN '2022-09-18' AND '2022-10-20'
group by customer_name
HAVING SUM(FORMAT(ROUND((total_products+total_shipping_tax_excl),2),2,'de_DE')) > 1
;