[SQL] Combineren van twee tabellen

[MIster X]

Ik ben bezig met wat statistieken, maar kom er met met query's niet helemaal uit. Ik wil graag een tabel met voor elke maand het aantal inschrijvingen en daarnaast het aantal bestellingen voor die maand.

Met de volgende query verzamel ik de inschrijvingen van het afgelopen jaar:

code:

SELECT YEAR (inschrijfdatum) AS jaar, MONTH (inschrijfdatum) AS maand, 
     count(userID) as inschrijvingen 
FROM cs_users 
GROUP BY jaar DESC, maand DESC
LIMIT 12;

jaar    maand   inschrijvingen
2004    8   95
2004    7   82
2004    6   54
2004    5   54
2004    4   25
2004    3   52
2004    2   59
2004    1   234
2003    12  49
2003    10  4
0   0   2

En de bestellingen per maand:

code:

SELECT YEAR (status_date ) AS jaar, MONTH (status_date) AS maand, 
     count(status_auto_id) as bestellingen 
FROM cs_bestellingstatus
WHERE status_ID = '2'
GROUP BY jaar DESC, maand DESC
LIMIT 12;

jaar    maand   bestellingen
2004    8   85
2004    7   79
2004    6   45
2004    5   25

Dat ging allebei goed en de waarden zijn correct.

Maar nu wil ik die twee query's combineren. Zoals ik het begrijp, moet ik de tabellen joinen? Maar dat gaat bij mij niet goed:

code:

SELECT YEAR (A.inschrijfdatum) AS jaar, MONTH (A.inschrijfdatum) AS maand, 
     count(A.userID) as inschrijvingen, 
     count(B.status_auto_id) as bestellingen  
FROM cs_users A 
LEFT JOIN cs_bestellingstatus B 
ON A.inschrijfdatum = B.status_date AND B.status_ID = '2'
GROUP BY jaar DESC, maand DESC

jaar    maand   inschrijvingen  bestellingen
2004    8   326 323
2004    7   290 289
2004    6   108 105
2004    5   150 127
2004    4   25  0
2004    3   52  0
2004    2   59  0
2004    1   234 0
2003    12  49  0
2003    10  4   0
0   0   2   0

Tot en met het moment dat de bestellingen moeten worden toegevoegd aan de tabel, gaat het goed. Maar wat gebeurd er daarna met de waarden? Het is geen optellen. Maar het zijn ook niet de goede waarden

Is er iemand die me even op weg kant helpen cq een hint kan geven?

[ Voor 12% gewijzigd door MIster X op 31-08-2004 16:08 ]

[bigbeng]

De informatie van je data is vrij uitgebreid, maar wat probeer je te bereiken?

[Gé Brander]

Als ik je goed begrijp wil je voor maand 8, 85 en 95 opgeteld hebben?

[MIster X]

Nee, ik wil graag de kolommen inschrijvingen en bestellingen naast elkaar...

ff openingspost aangepast.

[ Voor 17% gewijzigd door MIster X op 31-08-2004 15:45 ]

[Maasluip]

Je joint de inschrijvingsdatum met de status_date, dat betekent dat je alleen bestellingen zult vinden die op de inschrijfdatum zijn gemaakt, en dat zijn er waarschijnlijk niet gek veel.

Ik neem aan dat in cs_bestellingstatus ook een veld staat dat de user beschrijft en dat dezelfde waarde aanneemt als een veld dat in cs_users staat. Die twee moet je met elkaar joinen.

Welke velden heb je in beide tabellen? Dat is ook verhelderend.

[Verwijderd]

SELECT YEAR (A.inschrijfdatum) AS jaar, MONTH (A.inschrijfdatum) AS maand,
count(A.userID) as inschrijvingen,
count(B.status_auto_id) as bestellingen
FROM cs_users A, cs_bestellingstatus B
WHERE A.inschrijfdatum = B.status_date AND B.status_ID = '2'
GROUP BY jaar DESC, maand DESC

[MIster X]

Maasluip schreef op 31 augustus 2004 @ 15:44:
Je joint de inschrijvingsdatum met de status_date, dat betekent dat je alleen bestellingen zult vinden die op de inschrijfdatum zijn gemaakt, en dat zijn er waarschijnlijk niet gek veel.

Hmm, ik moet de maanden joinen, niet de datums zelf... ?

Ik neem aan dat in cs_bestellingstatus ook een veld staat dat de user beschrijft en dat dezelfde waarde aanneemt als een veld dat in cs_users staat. Die twee moet je met elkaar joinen.

Welke velden heb je in beide tabellen? Dat is ook verhelderend.

Veel velden. Maar had gaat me hier enkel om het aantal records binnen een bepaalde periode. Daarom lijkt het me ook niet logisch om de user-id's met elkaar te joinen, want het gaat met niet om het aantal bestellingen van die klanten, maar gewoon om het totaal.

Voor m'n gevoel moet de oplossing voor de hand liggen. Met 2 query's is dit in PHP zo gemaakt, hoor. Maar het moet toch ook gewoon in 1 query kunnen?

[MIster X]

Verwijderd schreef op 31 augustus 2004 @ 15:51:
SELECT YEAR (A.inschrijfdatum) AS jaar, MONTH (A.inschrijfdatum) AS maand,
count(A.userID) as inschrijvingen,
count(B.status_auto_id) as bestellingen
FROM cs_users A, cs_bestellingstatus B
WHERE A.inschrijfdatum = B.status_date AND B.status_ID = '2'
GROUP BY jaar DESC, maand DESC

Nee, want dan krijg je als resultaat
a. alleen die maanden die in beide tabellen voorkomen; geen NULL-waarden en die zijn er wel degelijk (vóór mei geen bestellingen geregistreerd).
b. foute waarden (zie correct waarden in openingspost); maar waarom zijn ze fout?

code:

jaar    maand   inschrijvingen  bestellingen
2004    8   323 323
2004    7   289 289
2004    6   105 105
2004    5   127 127

Iets met UNION? Maar dan komen de tabellen onder elkaar te staan...

[ Voor 45% gewijzigd door MIster X op 31-08-2004 16:15 ]

[andrew]

MIster X schreef op 31 augustus 2004 @ 15:59:
Iets met UNION? Maar dan komen de tabellen onder elkaar te staan...

Nee UNION doet iets heel anders, die zet ze onder elkaar idd.

LEFT JOIN is inderdaad goed (dat lost het probleem met die null waardes ook op)

Maar ik denk dat je foutje hier zit:

SELECT YEAR (A.inschrijfdatum) AS jaar, MONTH (A.inschrijfdatum) AS maand,
count(A.userID) as inschrijvingen,
count(B.status_auto_id) as bestellingen
FROM cs_users A
LEFT JOIN cs_bestellingstatus B
ON A.inschrijfdatum = B.status_date AND B.status_ID = '2'
GROUP BY jaar DESC, maand DESC

[Weng]

Ik heb dit eerlijk gezegd nog nooit gezien:

ON A.inschrijfdatum = B.status_date AND B.status_ID = '2'

Bij een join.

Ik doe dit normaal gesproken ipv AND -> WHERE:

SQL:

SELECT YEAR (A.inschrijfdatum) AS jaar, MONTH (A.inschrijfdatum) AS maand, 
count(A.userID) as inschrijvingen, 
count(B.status_auto_id) as bestellingen 
FROM cs_users A 
LEFT JOIN cs_bestellingstatus B 
ON A.inschrijfdatum = B.status_date 
WHERE B.status_ID = '2'
GROUP BY jaar DESC, maand DESC

Ik weet niet of dit de fout kan zijn.

[djluc]

Weng schreef op 31 augustus 2004 @ 19:15:
Ik heb dit eerlijk gezegd nog nooit gezien:
ON A.inschrijfdatum = B.status_date AND B.status_ID = '2'

Inderdaad vaag omdat het niets over de relatie zegt.

offtopic:
Ik vind je databaseopzet maar vreemd: waarom niet gewoon een tabel inschrijvingen met daarin 1 rij per inschrijving?

[MIster X]

Weng schreef op 31 augustus 2004 @ 19:15:
Ik heb dit eerlijk gezegd nog nooit gezien:

ON A.inschrijfdatum = B.status_date AND B.status_ID = '2'

Bij een join.

Ik doe dit normaal gesproken ipv AND -> WHERE:

SQL:

SELECT YEAR (A.inschrijfdatum) AS jaar, MONTH (A.inschrijfdatum) AS maand, 
count(A.userID) as inschrijvingen, 
count(B.status_auto_id) as bestellingen 
FROM cs_users A 
LEFT JOIN cs_bestellingstatus B 
ON A.inschrijfdatum = B.status_date 
WHERE B.status_ID = '2'
GROUP BY jaar DESC, maand DESC

Ik weet niet of dit de fout kan zijn.

djluc schreef op 31 augustus 2004 @ 19:26:
Ik vind je databaseopzet maar vreemd: waarom niet gewoon een tabel inschrijvingen met daarin 1 rij per inschrijving?

Uhm... de tabel inschrijvingen heeft 1 rij per inschrijving. Maar een bestelling kan meerdere statussen hebben. In de tabel met bestellingen staat geen datum, dat wordt bijgehouden in de tabel met statussen. Bij status #2 is de bestelling 'Ontvangen' (op datum xx-xx-xxxx), de andere statussen zijn hier niet van belang (bevestingen, klaar voor afhalen, etc.). Dus die conditie is wel belangrijk.

De plaats van de conditie is wel goed. Was trouwens ook nieuw voor mij. In één van de commentaren op http://dev.mysql.com/doc/mysql/en/JOIN.html staat: "Conditions for the 'right table' go in the ON clause. Conditions for the 'left table' go in the WHERE clause, except for the joining conditions themselves."