Toon posts:

[mysql] Totale omzet per klant berekenen via een join

Pagina: 1
Acties:

Vraag


  • knarfyboy
  • Registratie: November 2001
  • Laatst online: 12-08 23:13
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 michael@email.com een omzet van 260000 genereerd, in plaats van 2 losse rijen.

Mijn query is als volgt:
code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
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?

Alle reacties


Acties:
  • +2Henk 'm!

  • Woy
  • Registratie: April 2000
  • Niet online

Woy

Moderator Devschuur®
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:
1
2
3
4
5
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]

“Build a man a fire, and he'll be warm for a day. Set a man on fire, and he'll be warm for the rest of his life.”


  • knarfyboy
  • Registratie: November 2001
  • Laatst online: 12-08 23:13
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.



Nintendo Switch (OLED model) Apple iPhone SE (2022) LG G1 Google Pixel 6 Call of Duty: Vanguard Samsung Galaxy S22 Garmin fēnix 7 Nintendo Switch Lite

Tweakers vormt samen met Hardware Info, AutoTrack, Gaspedaal.nl, Nationale Vacaturebank, Intermediair en Independer DPG Online Services B.V.
Alle rechten voorbehouden © 1998 - 2022 Hosting door True

Tweakers maakt gebruik van cookies

Tweakers plaatst functionele en analytische cookies voor het functioneren van de website en het verbeteren van de website-ervaring. Deze cookies zijn noodzakelijk. Om op Tweakers relevantere advertenties te tonen en om ingesloten content van derden te tonen (bijvoorbeeld video's), vragen we je toestemming. Via ingesloten content kunnen derde partijen diensten leveren en verbeteren, bezoekersstatistieken bijhouden, gepersonaliseerde content tonen, gerichte advertenties tonen en gebruikersprofielen opbouwen. Hiervoor worden apparaatgegevens, IP-adres, geolocatie en surfgedrag vastgelegd.

Meer informatie vind je in ons cookiebeleid.

Sluiten

Toestemming beheren

Hieronder kun je per doeleinde of partij toestemming geven of intrekken. Meer informatie vind je in ons cookiebeleid.

Functioneel en analytisch

Deze cookies zijn noodzakelijk voor het functioneren van de website en het verbeteren van de website-ervaring. Klik op het informatie-icoon voor meer informatie. Meer details

janee

    Relevantere advertenties

    Dit beperkt het aantal keer dat dezelfde advertentie getoond wordt (frequency capping) en maakt het mogelijk om binnen Tweakers contextuele advertenties te tonen op basis van pagina's die je hebt bezocht. Meer details

    Tweakers genereert een willekeurige unieke code als identifier. Deze data wordt niet gedeeld met adverteerders of andere derde partijen en je kunt niet buiten Tweakers gevolgd worden. Indien je bent ingelogd, wordt deze identifier gekoppeld aan je account. Indien je niet bent ingelogd, wordt deze identifier gekoppeld aan je sessie die maximaal 4 maanden actief blijft. Je kunt deze toestemming te allen tijde intrekken.

    Ingesloten content van derden

    Deze cookies kunnen door derde partijen geplaatst worden via ingesloten content. Klik op het informatie-icoon voor meer informatie over de verwerkingsdoeleinden. Meer details

    janee