[sql] 4 tabellen 2x groupby?
orders
id
abo
fakturen
faktuurnr
datum (DATE)
order_id
faktuurregels
faktuurnr
omschrijving
prijs
abonnementen
id
abo_naam
Relaties:
fakturen.order_id = orders.id
orders.abo = abonnementen.id
fakturen.faktuurnr = faktuurregels.faktuurnr
Wat ik wil bereiken is een overzicht per MAAND van alle faktuurbedragen per ABONNEMENT.
Dus als volgt:
--
2007-07
Lite : 7853.80
Basic : 4648.78
Extra : 1359.03
--
of als sql result:
Code (php)
1
2
3
4
5
6
7
8
9
2
3
4
5
6
7
8
9
2007-07 | lite | 12,98
2007-07 | basic | 15,98
2007-07 | extra | 12,97
2007-06 | lite | 22,98
2007-06 | basic | 42,18
2007-06 | extra | 132,98
2007-05 | lite | 12,98
2007-05 | basic | 13,98
2007-05 | extra | 98,93
2007-07 | basic | 15,98
2007-07 | extra | 12,97
2007-06 | lite | 22,98
2007-06 | basic | 42,18
2007-06 | extra | 132,98
2007-05 | lite | 12,98
2007-05 | basic | 13,98
2007-05 | extra | 98,93
Dat wil dus zeggen, in Juli 2007 is voor alle lite orders totaal 7853.80 euro omzet gemaakt.
Ik ben dr al een halve dag mee aant klooien maar ik krijg het niet voor elkaar in 1 query.
Kan dit in 1 query uberhaupt? Wie maakt me los..
Heb het nu in 2 querys als volgt:
Code (php)
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
32
33
34
35
36
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
32
33
34
35
36
<?
$sql_maanden = 'SELECT DISTINCT
f.datum
FROM
fakturen f
WHERE
f.order_id != "0"
ORDER BY
f.datum ASC';
$sql = 'SELECT
f.datum,
SUM(r.prijs) AS totaalprijs,
ab.abo_naam,
ab.id AS abo_id
FROM
fakturen f,
faktuurregels r,
orders a,
abonnementen ab
WHERE
r.faktuurnr = f.faktuurnr
AND
f.datum = "'.$row_maanden['datum'].'"
AND
f.order_id != "0"
AND
a.id = f.order_id
AND
a.abo = ab.id
GROUP BY
a.abo
ORDER BY
ab.id ASC';
?>
$sql_maanden = 'SELECT DISTINCT
f.datum
FROM
fakturen f
WHERE
f.order_id != "0"
ORDER BY
f.datum ASC';
$sql = 'SELECT
f.datum,
SUM(r.prijs) AS totaalprijs,
ab.abo_naam,
ab.id AS abo_id
FROM
fakturen f,
faktuurregels r,
orders a,
abonnementen ab
WHERE
r.faktuurnr = f.faktuurnr
AND
f.datum = "'.$row_maanden['datum'].'"
AND
f.order_id != "0"
AND
a.id = f.order_id
AND
a.abo = ab.id
GROUP BY
a.abo
ORDER BY
ab.id ASC';
?>
Maar das dus niets van wat ik wil bereiken. Ik zal dan bijv 2x een groupby moeten geven wat niet wil.
iemand suggesties / voorzetje
bvd
PS. NEEN ik kan de database layout niet wijzigen
Gewijzigd op 01/01/1970 01:00:00 door - wes -
okidoki dan, toevoeging, een voorzetje is zeer welkom, maar wel met JOINS.
Gewijzigd op 01/01/1970 01:00:00 door - wes -
Als je nog een keer goed kijkt dan zie je dat hij al JOINs gebruikt.
Edit:
tabel1.column en tabel2.column is ook een JOIN
@Wes
Ik heb me niet verdiept in je tabellen, want ik ben net wakker en dus lui, maar als die 2 queries werken, dan kun je er ook gewoon een subquery van maken en IN gebruiken, toch?
Code (php)
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
32
33
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
32
33
<?
SELECT
f.datum,
SUM(r.prijs) AS totaalprijs,
ab.abo_naam,
ab.id AS abo_id
FROM
fakturen f,
faktuurregels r,
orders a,
abonnementen ab
WHERE
r.faktuurnr = f.faktuurnr
AND
f.datum IN ( SELECT DISTINCT
f.datum
FROM
fakturen f
WHERE
f.order_id != "0"
ORDER BY
f.datum ASC )
AND
f.order_id != "0"
AND
a.id = f.order_id
AND
a.abo = ab.id
GROUP BY
a.abo
ORDER BY
ab.id ASC
?>
SELECT
f.datum,
SUM(r.prijs) AS totaalprijs,
ab.abo_naam,
ab.id AS abo_id
FROM
fakturen f,
faktuurregels r,
orders a,
abonnementen ab
WHERE
r.faktuurnr = f.faktuurnr
AND
f.datum IN ( SELECT DISTINCT
f.datum
FROM
fakturen f
WHERE
f.order_id != "0"
ORDER BY
f.datum ASC )
AND
f.order_id != "0"
AND
a.id = f.order_id
AND
a.abo = ab.id
GROUP BY
a.abo
ORDER BY
ab.id ASC
?>
Edit:
Het is misschien een beetje vieze oplossing maar het zal wel een stuk sneller zijn dan een PHP oplossing. Ik kijk zo wel of ik het in een mooie query kan doen.
Edit:
Bij nader inzien denk ik niet dat bovenstaande query gaat werken aangezien je natuurlijk gewoon elke datum langs wil gaan
Gewijzigd op 01/01/1970 01:00:00 door PHP erik
De tweede query heeft wat extra's nodig, de GROUP BY moet je uitbreiden met alle gegevens die je opvraagt (behalve r.prijs, die staat namelijk in de SUM) en de datum moet zijn het jaar + de maand.
Verder natuurlijk dubbele quotes gebruiken om de query en enkele quotes om een string in de query:
Code (php)
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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
<?php
$sql = "SELECT
DATE_FORMAT(f.datum, '%Y-%m') AS jaarmaand,
SUM(r.prijs) AS totaalprijs,
ab.abo_naam,
ab.id AS abo_id
FROM
fakturen f,
faktuurregels r,
orders a,
abonnementen ab
WHERE
r.faktuurnr = f.faktuurnr
AND
f.order_id != '0'
AND
a.id = f.order_id
AND
a.abo = ab.id
GROUP BY
a.abo, ab.abo_naam, DATE_FORMAT(f.datum, '%Y-%m')
ORDER BY
f.datum ASC,
ab.id ASC";
?>
$sql = "SELECT
DATE_FORMAT(f.datum, '%Y-%m') AS jaarmaand,
SUM(r.prijs) AS totaalprijs,
ab.abo_naam,
ab.id AS abo_id
FROM
fakturen f,
faktuurregels r,
orders a,
abonnementen ab
WHERE
r.faktuurnr = f.faktuurnr
AND
f.order_id != '0'
AND
a.id = f.order_id
AND
a.abo = ab.id
GROUP BY
a.abo, ab.abo_naam, DATE_FORMAT(f.datum, '%Y-%m')
ORDER BY
f.datum ASC,
ab.id ASC";
?>
Niet getest, dus garantie tot aan de deur!
Edit: Haakjes verwijderd, query werkt nu goed.
Gewijzigd op 01/01/1970 01:00:00 door Frank -
tnx frank erik!
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
<?php
SELECT
MONTH(b.datum) AS maand,
SUM(c.prijs) AS maandtotaal,
d.abo_naam
FROM
orders AS a,
fakturen AS b,
faktuurregels AS c,
abonnementen AS d
WHERE
a.abo = d.id
AND
a.id = b.order_id
AND
b.faktuurnummer = c.faktuurnummer
AND
b.order_id <> 0
GROUP BY
d.id, MONTH(b.datum)
ORDER BY
b.datum
DESC
?>
SELECT
MONTH(b.datum) AS maand,
SUM(c.prijs) AS maandtotaal,
d.abo_naam
FROM
orders AS a,
fakturen AS b,
faktuurregels AS c,
abonnementen AS d
WHERE
a.abo = d.id
AND
a.id = b.order_id
AND
b.faktuurnummer = c.faktuurnummer
AND
b.order_id <> 0
GROUP BY
d.id, MONTH(b.datum)
ORDER BY
b.datum
DESC
?>
Edit:
Te laat
Gewijzigd op 01/01/1970 01:00:00 door PHP erik
Gewijzigd op 01/01/1970 01:00:00 door PHP erik
ik ben btw wes en die boven je is frank