[mysql] Totale omzet per klant berekenen via een join

[knarfyboy]

Heb een probleem met een query en ik kom er zo 123 niet uit. Ik heb 3 tabellen:

customers


products


sales


Ik wil per emailadres uit customers het totaal aantal producten berekenen (een count van het aantal rijen in sales), en de totale omzet (aantal rijen in sales * price in products).

Nu heb ik bijna wat ik wil:


Het enige probleem is dat ik ze niet gegroepeerd krijg per email adres. Ik wil dat de klant [e-mail] een omzet van 260000 genereerd, in plaats van 2 losse rijen.

Mijn query is als volgt:

code:

select
    c.email,
    total_products,
    revenue
from
    customers c
left join
    (select 
        product_id,
        email,
        count(product_id) as total_products,
        products.price * count(sales.product_id) as revenue
    from 
        sales,
        products
    where
        products.id = sales.product_id
    group by 
            sales.product_id,
            email
    ) s on s.email = c.email
left join
    (
    select
        products.id as product_id, 
        products.name as product_name
    from 
        products
    group by 
        products.id
    ) p on p.product_id = s.product_id

Als ik een group by c.email doe dan pakt hij alleen 1 rij die hij vindt van bij klant maar dit is niet wat ik wil (ik wil het totaal van alle rijen in sales):


Hoe kan ik dit voor elkaar krijgen?

[Woy]

Aangezien sales geen aantal heeft is het vrij eenvoudig ( Met aantal zou het overigens maar een kleine change zijn, want binnen de SUM/COUNT kun je ook gewoon een expressie gebruiken )

SQL:

SELECT s.email, COUNT(*) AS total_products, SUM(p.price) AS revenue
FROM sales s
INNER JOIN products p
    ON s.product_id = p.id
GROUP BY s.email

De query die jij toont is echt overmatig complex met joins op sub-query's e.d.

Dit is waarschijnlijk een testje, maar het data model lijkt mij niet erg solide. Voor verkopen wil je niet afhankelijk zijn van de prijs in de product tabel, maar die overeengekomen prijs wil je opnemen in de orderline ( jouw sales ) tabel. Immers wil je niet dat als later de prijs veranderd, dat nog impact heeft op je historische verkopen.

Tevens zou ik niet het e-mail adres gebruiken als primary-key voor je customers en foreign key in sales. Ik zou altijd een syntetische sleutel gebruiken zoals je ook in products doet. Dat geeft je de mogelijkheid om het mail-adres later nog te wijzigen, en grote kans dat het nog minder ruimte gebruikt, en sneller is.

[ Voor 33% gewijzigd door Woy op 09-03-2021 09:00 ]

[knarfyboy]

Je hebt gelijk, de data is op niet op een optimale manier gestructureerd en ik zou het nooit zo op deze manier indelen (dit is even een versimpeld voorbeeld van de echte data).

Alle nieuwe data komt nog steeds op deze manier binnen in deze webshop, hier kan ik helaas niets aan veranderen.