4 tabellen combineren en resultaat weergeven
Geef de structuur van je tabellen eens door en wat voorbeeld data.
Dan zal ik het aan mijn kant eens nabouwen.
Bij voorkeur de sql statements, dat is het eenvoudigst voor mij. via phpMyAdmin Export makkelijk te maken.
Gewijzigd op 14/12/2015 21:07:37 door Jan de Laet
Toevoeging op 14/12/2015 21:26:13:
Ik heb de inhoud even gekopieëerd en geplakt in een pm daarnet (3 pm's)
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT
id, titel, bron,
SUM(CASE WHEN (lv.cont_status='anoniem') THEN lv.aantal ELSE 0 END) AS SumLvAnoniem1,
SUM(CASE WHEN (lv.cont_status='koud') THEN lv.aantal ELSE 0 END) AS SumLvKoud1,
SUM(CASE WHEN (lv.cont_status='lead') THEN lv.aantal ELSE 0 END) AS SumLvLead1,
SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect1,
SUM(CASE WHEN (lv.cont_status='klant') THEN lv.aantal ELSE 0 END) AS SumLvKlant1,
SUM(CASE WHEN (lv.cont_status='pool') THEN lv.aantal ELSE 0 END) AS SumLvPool1
FROM academy
JOIN (SELECT DISTINCT cont_status FROM contacten) AS c
JOIN
(SELECT academy_id, cont_status, bron, count(*) as aantal
FROM academy_landingviews alv
LEFT OUTER JOIN contacten c ON c.id=alv.user_id
GROUP BY academy_id, cont_status, bron) AS lv
ON academy.id=lv.academy_id and c.cont_status=lv.cont_status
WHERE id= 11
GROUP BY id, titel, bron
id, titel, bron,
SUM(CASE WHEN (lv.cont_status='anoniem') THEN lv.aantal ELSE 0 END) AS SumLvAnoniem1,
SUM(CASE WHEN (lv.cont_status='koud') THEN lv.aantal ELSE 0 END) AS SumLvKoud1,
SUM(CASE WHEN (lv.cont_status='lead') THEN lv.aantal ELSE 0 END) AS SumLvLead1,
SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect1,
SUM(CASE WHEN (lv.cont_status='klant') THEN lv.aantal ELSE 0 END) AS SumLvKlant1,
SUM(CASE WHEN (lv.cont_status='pool') THEN lv.aantal ELSE 0 END) AS SumLvPool1
FROM academy
JOIN (SELECT DISTINCT cont_status FROM contacten) AS c
JOIN
(SELECT academy_id, cont_status, bron, count(*) as aantal
FROM academy_landingviews alv
LEFT OUTER JOIN contacten c ON c.id=alv.user_id
GROUP BY academy_id, cont_status, bron) AS lv
ON academy.id=lv.academy_id and c.cont_status=lv.cont_status
WHERE id= 11
GROUP BY id, titel, bron
Er is een kleine wijziging de LEFT OUTER JOIN na de subquery is een JOIN geworden.
Dit geeft op basis van jouw data dit resultaat voor id 11:
Code (php)
1
2
3
4
2
3
4
Id Titel Bron A Ko L Pr Kl Po (Anoniem, Klant, Lead, Prospect, Klant, Pool
11 Titel11 0 0 0 0 1 0
11 Titel11 facebook 2 0 0 0 2 0
11 Titel11 website 1 0 0 0 0 0
11 Titel11 0 0 0 0 1 0
11 Titel11 facebook 2 0 0 0 2 0
11 Titel11 website 1 0 0 0 0 0
En die getallen kloppen met je gegevens.
Gewijzigd op 14/12/2015 22:17:36 door Jan de Laet
Alleen heb ik geen bron bij al hetgeen via de website is binnengekomen (en dus niet van een externe link zoals facebook bvb). Kan ik op de een of andere manier diegene die geen bron vermelding hebben op website zetten via de query? Of moet ik mijn scripten aanpassen dat er toch altijd een bron is (indien geen opgegeven moet dit automatisch 'website' worden)?
Toevoeging op 14/12/2015 22:31:29:
En als ik in principe hetzelfde wil doen met de download kant ook dan moet ik waarschijnlijk terug een subquery in een join ertussen steken en een paar SUM's toevoegen?
Gewijzigd op 14/12/2015 22:23:16 door Brecht S
Maar eventueel kan CASE je hier helpen.
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT
id, titel, bron,
SUM(CASE WHEN (lv.cont_status='anoniem') THEN lv.aantal ELSE 0 END) AS SumLvAnoniem1,
SUM(CASE WHEN (lv.cont_status='koud') THEN lv.aantal ELSE 0 END) AS SumLvKoud1,
SUM(CASE WHEN (lv.cont_status='lead') THEN lv.aantal ELSE 0 END) AS SumLvLead1,
SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect1,
SUM(CASE WHEN (lv.cont_status='klant') THEN lv.aantal ELSE 0 END) AS SumLvKlant1,
SUM(CASE WHEN (lv.cont_status='pool') THEN lv.aantal ELSE 0 END) AS SumLvPool1
FROM academy
JOIN (SELECT DISTINCT cont_status FROM contacten) AS c
JOIN
(SELECT academy_id, cont_status,
case when bron='' then 'website' else bron end as bron,
count(*) as aantal
FROM academy_landingviews alv
LEFT OUTER JOIN contacten c ON c.id=alv.user_id
GROUP BY academy_id, cont_status, bron) AS lv
ON academy.id=lv.academy_id and c.cont_status=lv.cont_status
WHERE id= 11
GROUP BY id, titel, bron
id, titel, bron,
SUM(CASE WHEN (lv.cont_status='anoniem') THEN lv.aantal ELSE 0 END) AS SumLvAnoniem1,
SUM(CASE WHEN (lv.cont_status='koud') THEN lv.aantal ELSE 0 END) AS SumLvKoud1,
SUM(CASE WHEN (lv.cont_status='lead') THEN lv.aantal ELSE 0 END) AS SumLvLead1,
SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect1,
SUM(CASE WHEN (lv.cont_status='klant') THEN lv.aantal ELSE 0 END) AS SumLvKlant1,
SUM(CASE WHEN (lv.cont_status='pool') THEN lv.aantal ELSE 0 END) AS SumLvPool1
FROM academy
JOIN (SELECT DISTINCT cont_status FROM contacten) AS c
JOIN
(SELECT academy_id, cont_status,
case when bron='' then 'website' else bron end as bron,
count(*) as aantal
FROM academy_landingviews alv
LEFT OUTER JOIN contacten c ON c.id=alv.user_id
GROUP BY academy_id, cont_status, bron) AS lv
ON academy.id=lv.academy_id and c.cont_status=lv.cont_status
WHERE id= 11
GROUP BY id, titel, bron
Ik vervang hier een lege bron door 'website' in de subquery
Dus voor zover ik het nog begrijp, heb je de query voor de landing_view plus download totalen per id. Die toont dus 2 x 6 totalen
Binnen de lus waarin je deze toont, heb je telkens een 2e query voor de bron vermelding van landing_views. De resultaten hiervan kun je tonen op vervolgregels in je html tabel, nog eens 6 totalen per bron 1 regel.
Kan ik dus een nieuwe subquery JOINen de tabel downloads? Zoals we op de eerst besproken (in het begin van het topic) query hadden, op dezelfde manier?
Toevoeging op 14/12/2015 22:50:06:
Zoiets dus:
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
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
SELECT
id, titel, bron,
SUM(CASE WHEN (lv.cont_status='anoniem') THEN lv.aantal ELSE 0 END) AS SumLvAnoniem1,
SUM(CASE WHEN (lv.cont_status='koud') THEN lv.aantal ELSE 0 END) AS SumLvKoud1,
SUM(CASE WHEN (lv.cont_status='lead') THEN lv.aantal ELSE 0 END) AS SumLvLead1,
SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect1,
SUM(CASE WHEN (lv.cont_status='klant') THEN lv.aantal ELSE 0 END) AS SumLvKlant1,
SUM(CASE WHEN (lv.cont_status='pool') THEN lv.aantal ELSE 0 END) AS SumLvPool1,
SUM(CASE WHEN (dl.cont_status='anoniem') THEN dl.aantal ELSE 0 END) AS SumDlAnoniem1
FROM academy
JOIN (SELECT DISTINCT cont_status FROM contacten) AS c
JOIN
(SELECT academy_id, cont_status,
(case when bron='' then 'website' else bron end) as bron, count(*) as aantal
FROM academy_landingviews alv
LEFT OUTER JOIN contacten c ON c.id=alv.user_id
GROUP BY academy_id, cont_status, bron) AS lv
ON academy.id=lv.academy_id and c.cont_status=lv.cont_status
JOIN
(SELECT academy_id, cont_status,
(case when bron='' then 'website' else bron end) as bron, count(*) as aantal
FROM academy_download adl
JOIN contacten c ON c.id=adl.user_id
GROUP BY academy_id, cont_status, bron) AS dl
ON academy.id=dl.academy_id and c.cont_status=dl.cont_status
WHERE id= '$academy_id'
GROUP BY id, titel, bron
id, titel, bron,
SUM(CASE WHEN (lv.cont_status='anoniem') THEN lv.aantal ELSE 0 END) AS SumLvAnoniem1,
SUM(CASE WHEN (lv.cont_status='koud') THEN lv.aantal ELSE 0 END) AS SumLvKoud1,
SUM(CASE WHEN (lv.cont_status='lead') THEN lv.aantal ELSE 0 END) AS SumLvLead1,
SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect1,
SUM(CASE WHEN (lv.cont_status='klant') THEN lv.aantal ELSE 0 END) AS SumLvKlant1,
SUM(CASE WHEN (lv.cont_status='pool') THEN lv.aantal ELSE 0 END) AS SumLvPool1,
SUM(CASE WHEN (dl.cont_status='anoniem') THEN dl.aantal ELSE 0 END) AS SumDlAnoniem1
FROM academy
JOIN (SELECT DISTINCT cont_status FROM contacten) AS c
JOIN
(SELECT academy_id, cont_status,
(case when bron='' then 'website' else bron end) as bron, count(*) as aantal
FROM academy_landingviews alv
LEFT OUTER JOIN contacten c ON c.id=alv.user_id
GROUP BY academy_id, cont_status, bron) AS lv
ON academy.id=lv.academy_id and c.cont_status=lv.cont_status
JOIN
(SELECT academy_id, cont_status,
(case when bron='' then 'website' else bron end) as bron, count(*) as aantal
FROM academy_download adl
JOIN contacten c ON c.id=adl.user_id
GROUP BY academy_id, cont_status, bron) AS dl
ON academy.id=dl.academy_id and c.cont_status=dl.cont_status
WHERE id= '$academy_id'
GROUP BY id, titel, bron
Alleen heb ik hier een foutmelding 'Column 'bron' in field list is ambiguous'
Gewijzigd op 14/12/2015 22:52:18 door Brecht S
Code (php)
1
2
3
4
5
6
7
8
2
3
4
5
6
7
8
JOIN
(SELECT academy_id, cont_status,
case when bron='' then 'website' else bron end as bron,
count(*) as aantal
FROM academy_download ad
LEFT OUTER JOIN contacten c ON c.id=ad.user_id
GROUP BY academy_id, cont_status, bron) AS dl
ON academy.id=dl.academy_id and c.cont_status=dl.cont_status
(SELECT academy_id, cont_status,
case when bron='' then 'website' else bron end as bron,
count(*) as aantal
FROM academy_download ad
LEFT OUTER JOIN contacten c ON c.id=ad.user_id
GROUP BY academy_id, cont_status, bron) AS dl
ON academy.id=dl.academy_id and c.cont_status=dl.cont_status
en in de SELECT de SUM regels herhalen voor de dl velden
Ik heb het 'bron' kolom probleem (zie mysql error) weggewerkt zoals je kan zien hieronder, maar dat zal waarschijnlijk ook niet echt juist zijn aangezien de cijfers nu niet meer juist zijn:
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
37
38
39
40
41
42
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
37
38
39
40
41
42
id, titel, bron,
SUM(CASE WHEN (lv.cont_status='anoniem') THEN lv.aantal ELSE 0 END) AS SumLvAnoniem1,
SUM(CASE WHEN (lv.cont_status='koud') THEN lv.aantal ELSE 0 END) AS SumLvKoud1,
SUM(CASE WHEN (lv.cont_status='lead') THEN lv.aantal ELSE 0 END) AS SumLvLead1,
SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect1,
SUM(CASE WHEN (lv.cont_status='klant') THEN lv.aantal ELSE 0 END) AS SumLvKlant1,
SUM(CASE WHEN (lv.cont_status='pool') THEN lv.aantal ELSE 0 END) AS SumLvPool1,
SUM(CASE WHEN (dl.cont_status='anoniem') THEN dl.aantal ELSE 0 END) AS SumDlAnoniem1,
SUM(CASE WHEN (dl.cont_status='koud') THEN dl.aantal ELSE 0 END) AS SumDlKoud1,
SUM(CASE WHEN (dl.cont_status='lead') THEN dl.aantal ELSE 0 END) AS SumDlLead1,
SUM(CASE WHEN (dl.cont_status='prospect') THEN dl.aantal ELSE 0 END) AS SumDlProspect1,
SUM(CASE WHEN (dl.cont_status='klant') THEN dl.aantal ELSE 0 END) AS SumDlKlant1,
SUM(CASE WHEN (dl.cont_status='pool') THEN dl.aantal ELSE 0 END) AS SumDlPool1
FROM academy
JOIN (SELECT DISTINCT cont_status FROM contacten) AS c
JOIN
(SELECT academy_id, cont_status,
(case when bron='' then 'website' else bron end) as bron, count(*) as aantal
FROM academy_landingviews alv
LEFT OUTER JOIN contacten c ON c.id=alv.user_id
GROUP BY academy_id, cont_status, bron) AS lv
ON academy.id=lv.academy_id and c.cont_status=lv.cont_status
JOIN
(SELECT academy_id, cont_status,
(case when bron='' then 'website' else bron end) as bron2, count(*) as aantal
FROM academy_download adl
JOIN contacten c ON c.id=adl.user_id
GROUP BY academy_id, cont_status, bron2) AS dl
ON academy.id=dl.academy_id and c.cont_status=dl.cont_status
WHERE id= '$academy_id'
GROUP BY id, titel, bron
SUM(CASE WHEN (lv.cont_status='anoniem') THEN lv.aantal ELSE 0 END) AS SumLvAnoniem1,
SUM(CASE WHEN (lv.cont_status='koud') THEN lv.aantal ELSE 0 END) AS SumLvKoud1,
SUM(CASE WHEN (lv.cont_status='lead') THEN lv.aantal ELSE 0 END) AS SumLvLead1,
SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect1,
SUM(CASE WHEN (lv.cont_status='klant') THEN lv.aantal ELSE 0 END) AS SumLvKlant1,
SUM(CASE WHEN (lv.cont_status='pool') THEN lv.aantal ELSE 0 END) AS SumLvPool1,
SUM(CASE WHEN (dl.cont_status='anoniem') THEN dl.aantal ELSE 0 END) AS SumDlAnoniem1,
SUM(CASE WHEN (dl.cont_status='koud') THEN dl.aantal ELSE 0 END) AS SumDlKoud1,
SUM(CASE WHEN (dl.cont_status='lead') THEN dl.aantal ELSE 0 END) AS SumDlLead1,
SUM(CASE WHEN (dl.cont_status='prospect') THEN dl.aantal ELSE 0 END) AS SumDlProspect1,
SUM(CASE WHEN (dl.cont_status='klant') THEN dl.aantal ELSE 0 END) AS SumDlKlant1,
SUM(CASE WHEN (dl.cont_status='pool') THEN dl.aantal ELSE 0 END) AS SumDlPool1
FROM academy
JOIN (SELECT DISTINCT cont_status FROM contacten) AS c
JOIN
(SELECT academy_id, cont_status,
(case when bron='' then 'website' else bron end) as bron, count(*) as aantal
FROM academy_landingviews alv
LEFT OUTER JOIN contacten c ON c.id=alv.user_id
GROUP BY academy_id, cont_status, bron) AS lv
ON academy.id=lv.academy_id and c.cont_status=lv.cont_status
JOIN
(SELECT academy_id, cont_status,
(case when bron='' then 'website' else bron end) as bron2, count(*) as aantal
FROM academy_download adl
JOIN contacten c ON c.id=adl.user_id
GROUP BY academy_id, cont_status, bron2) AS dl
ON academy.id=dl.academy_id and c.cont_status=dl.cont_status
WHERE id= '$academy_id'
GROUP BY id, titel, bron
En de bijhorende html tabel:
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
<tr>
<td><?php echo $row['id']; ?> - Landingviews door anoniem via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumLvAnoniem1']; ?></td>
<td>(<?php echo $row['id']; ?>)Downloads door anoniem via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumDlAnoniem1']; ?></td>
</tr>
<tr>
<td><?php echo $row['id']; ?> - Landingviews door koud via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumLvKoud1']; ?></td>
<td>(<?php echo $row['id']; ?>)Downloads door koud via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumDlKoud1']; ?></td>
</tr>
<tr>
<td><?php echo $row['id']; ?> - Landingviews door prospect via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumLvProspect1']; ?></td>
<td>(<?php echo $row['id']; ?>)Downloads door prospect via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumDlProspect1']; ?></td>
</tr>
<tr>
<td><?php echo $row['id']; ?> - Landingviews door klant via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumLvKlant1']; ?></td>
<td>(<?php echo $row['id']; ?>)Downloads door klant via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumDlKlant1']; ?></td>
</tr>
<tr>
<td><?php echo $row['id']; ?> - Landingviews door lead via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumLvLead1']; ?></td>
<td>(<?php echo $row['id']; ?>)Downloads door lead via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumDlLead1']; ?></td>
</tr>
<tr>
<td><?php echo $row['id']; ?> - Landingviews door pool via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumLvPool1']; ?></td>
<td>(<?php echo $row['id']; ?>)Downloads door pool via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumDlPool1']; ?></td>
</tr>
<td><?php echo $row['id']; ?> - Landingviews door anoniem via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumLvAnoniem1']; ?></td>
<td>(<?php echo $row['id']; ?>)Downloads door anoniem via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumDlAnoniem1']; ?></td>
</tr>
<tr>
<td><?php echo $row['id']; ?> - Landingviews door koud via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumLvKoud1']; ?></td>
<td>(<?php echo $row['id']; ?>)Downloads door koud via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumDlKoud1']; ?></td>
</tr>
<tr>
<td><?php echo $row['id']; ?> - Landingviews door prospect via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumLvProspect1']; ?></td>
<td>(<?php echo $row['id']; ?>)Downloads door prospect via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumDlProspect1']; ?></td>
</tr>
<tr>
<td><?php echo $row['id']; ?> - Landingviews door klant via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumLvKlant1']; ?></td>
<td>(<?php echo $row['id']; ?>)Downloads door klant via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumDlKlant1']; ?></td>
</tr>
<tr>
<td><?php echo $row['id']; ?> - Landingviews door lead via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumLvLead1']; ?></td>
<td>(<?php echo $row['id']; ?>)Downloads door lead via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumDlLead1']; ?></td>
</tr>
<tr>
<td><?php echo $row['id']; ?> - Landingviews door pool via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumLvPool1']; ?></td>
<td>(<?php echo $row['id']; ?>)Downloads door pool via <?php echo $row['bron']; ?></td>
<td><?php echo $row['SumDlPool1']; ?></td>
</tr>
Gewijzigd op 14/12/2015 23:15:15 door Brecht S
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
37
38
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
37
38
SELECT
id, titel, b.bron,
SUM(CASE WHEN (lv.cont_status='anoniem') THEN lv.aantal ELSE 0 END) AS SumLvAnoniem1,
SUM(CASE WHEN (lv.cont_status='koud') THEN lv.aantal ELSE 0 END) AS SumLvKoud1,
SUM(CASE WHEN (lv.cont_status='lead') THEN lv.aantal ELSE 0 END) AS SumLvLead1,
SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect1,
SUM(CASE WHEN (lv.cont_status='klant') THEN lv.aantal ELSE 0 END) AS SumLvKlant1,
SUM(CASE WHEN (lv.cont_status='pool') THEN lv.aantal ELSE 0 END) AS SumLvPool1,
SUM(CASE WHEN (dl.cont_status='anoniem') THEN dl.aantal ELSE 0 END) AS SumDlAnoniem1,
SUM(CASE WHEN (dl.cont_status='koud') THEN dl.aantal ELSE 0 END) AS SumDlKoud1,
SUM(CASE WHEN (dl.cont_status='lead') THEN dl.aantal ELSE 0 END) AS SumDlLead1,
SUM(CASE WHEN (dl.cont_status='prospect') THEN dl.aantal ELSE 0 END) AS SumDlProspect1,
SUM(CASE WHEN (dl.cont_status='klant') THEN dl.aantal ELSE 0 END) AS SumDlKlant1,
SUM(CASE WHEN (dl.cont_status='pool') THEN dl.aantal ELSE 0 END) AS SumDlPool1
FROM academy
JOIN (SELECT DISTINCT cont_status FROM contacten) AS c
JOIN (SELECT DISTINCT (case when bron='' then 'website' else bron end) as bron FROM academy_landingviews) AS b
LEFT OUTER JOIN
(SELECT academy_id, cont_status,
(case when bron='' then 'website' else bron end) as bron, count(*) as aantal
FROM academy_landingviews alv
LEFT OUTER JOIN contacten c ON c.id=alv.user_id
GROUP BY academy_id, cont_status, bron) AS lv
ON academy.id=lv.academy_id and c.cont_status=lv.cont_status and b.bron=lv.bron
LEFT OUTER JOIN
(SELECT academy_id, cont_status,
(case when bron='' then 'website' else bron end) as bron, count(*) as aantal
FROM academy_download adl
JOIN contacten c ON c.id=adl.user_id
GROUP BY academy_id, cont_status, bron) AS dl
ON academy.id=dl.academy_id and c.cont_status=dl.cont_status and b.bron=dl.bron
WHERE id= 11
GROUP BY id, titel, b.bron
id, titel, b.bron,
SUM(CASE WHEN (lv.cont_status='anoniem') THEN lv.aantal ELSE 0 END) AS SumLvAnoniem1,
SUM(CASE WHEN (lv.cont_status='koud') THEN lv.aantal ELSE 0 END) AS SumLvKoud1,
SUM(CASE WHEN (lv.cont_status='lead') THEN lv.aantal ELSE 0 END) AS SumLvLead1,
SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect1,
SUM(CASE WHEN (lv.cont_status='klant') THEN lv.aantal ELSE 0 END) AS SumLvKlant1,
SUM(CASE WHEN (lv.cont_status='pool') THEN lv.aantal ELSE 0 END) AS SumLvPool1,
SUM(CASE WHEN (dl.cont_status='anoniem') THEN dl.aantal ELSE 0 END) AS SumDlAnoniem1,
SUM(CASE WHEN (dl.cont_status='koud') THEN dl.aantal ELSE 0 END) AS SumDlKoud1,
SUM(CASE WHEN (dl.cont_status='lead') THEN dl.aantal ELSE 0 END) AS SumDlLead1,
SUM(CASE WHEN (dl.cont_status='prospect') THEN dl.aantal ELSE 0 END) AS SumDlProspect1,
SUM(CASE WHEN (dl.cont_status='klant') THEN dl.aantal ELSE 0 END) AS SumDlKlant1,
SUM(CASE WHEN (dl.cont_status='pool') THEN dl.aantal ELSE 0 END) AS SumDlPool1
FROM academy
JOIN (SELECT DISTINCT cont_status FROM contacten) AS c
JOIN (SELECT DISTINCT (case when bron='' then 'website' else bron end) as bron FROM academy_landingviews) AS b
LEFT OUTER JOIN
(SELECT academy_id, cont_status,
(case when bron='' then 'website' else bron end) as bron, count(*) as aantal
FROM academy_landingviews alv
LEFT OUTER JOIN contacten c ON c.id=alv.user_id
GROUP BY academy_id, cont_status, bron) AS lv
ON academy.id=lv.academy_id and c.cont_status=lv.cont_status and b.bron=lv.bron
LEFT OUTER JOIN
(SELECT academy_id, cont_status,
(case when bron='' then 'website' else bron end) as bron, count(*) as aantal
FROM academy_download adl
JOIN contacten c ON c.id=adl.user_id
GROUP BY academy_id, cont_status, bron) AS dl
ON academy.id=dl.academy_id and c.cont_status=dl.cont_status and b.bron=dl.bron
WHERE id= 11
GROUP BY id, titel, b.bron
Wat doe ik nu:
1. Ik maak een cross join tussen de titels en alle voorkomende status en bron SELECT plus 2 JOIN
2. Ik maak een subquery voor aantallen landing_views per titel status en bron
3. Ik maak een subquery voor aantallen downloads ook per titel status en bron
4. Deze voeg ik samen, en groepeer ze.
Ik raad je wel aan om 2 nieuwe tabellen te maken (mocht je die nog niet hebben) voor de toegestane waardes van status en van de bron.
Dan kan je de 2 JOINs vervangen door:
Code (php)
1
2
2
JOIN (SELECT cont_status FROM contact_values) AS c
JOIN (SELECT bron FROM bron_values) AS b
JOIN (SELECT bron FROM bron_values) AS b
Is wat mij betreft veel beter en ook wat netter.
Die aparte tabellen die je voorstelde heb ik niet. Maar ik wil het wel aanpassen zodat ik ze wel heb.
Gewijzigd op 21/12/2015 15:55:29 door Brecht S