Cookies op Tweakers

Tweakers is onderdeel van DPG Media en maakt gebruik van cookies, JavaScript en vergelijkbare technologie om je onder andere een optimale gebruikerservaring te bieden. Ook kan Tweakers hierdoor het gedrag van bezoekers vastleggen en analyseren. Door gebruik te maken van deze website, of door op 'Cookies accepteren' te klikken, geef je toestemming voor het gebruik van cookies. Wil je meer informatie over cookies en hoe ze worden gebruikt? Bekijk dan ons cookiebeleid.

Meer informatie
Toon posts:

[mysql] Totale omzet per klant berekenen via een join

Pagina: 1
Acties:

Vraag


  • knarfyboy
  • Registratie: november 2001
  • Laatst online: 24-05 21:12
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: 24-05 21:12
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.


Apple iPad Pro (2021) 11" Wi-Fi, 8GB ram Microsoft Xbox Series X LG CX Google Pixel 5a 5G Sony XH90 / XH92 Samsung Galaxy S21 5G Sony PlayStation 5 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 - 2021 Hosting door True