4 tabellen combineren en resultaat weergeven

Door Brecht S op 12-12-2015 11:25 gewijzigd op 12-12-2015 14:01

4.548 views

Ik ben al een tijdje aan het zoeken naar de juiste query maar geraak er niet aan uit. Hieronder een greep uit mijn 4 mysql tabellen die ik wil combineren:

Tabel academy_landingviews


id  user_id  academy_id

Tabel academy_download


id  user_id  academy_id

Tabel academy


id  titel

Tabel contacten


id  status

Nu is het zo dat ik een overzicht wil van alle ebooks die geplaatst zijn onder de tabel academy in combinatie met de status bij de contacten. De status kan bvb koud, lead, prospect of klant zijn.
Wat ik tot nu toe heb zitten proberen:


SELECT 
	a.titel, alv.academy_id, SUM(alv.view) as alvv, COUNT(ad.id) as adid
FROM 
	academy_landingviews alv
INNER JOIN
	academy_download ad
ON
	alv.academy_id = ad.academy_id
INNER JOIN
	academy a
ON
	alv.academy_id = a.id
INNER JOIN
	contacten c
ON
	alv.user_id = c.id
GROUP BY 
        alv.academy_id

Ik krijg nu een overzicht van alle resultaten grouped by de titel van een ebook. Maar nog niet in combinatie met de contacten status. Verder dan dit geraak ik niet. Iemand een suggestie?
Misschien is mijn query ook deels verkeerd. Geen idee...
Nu maak ik een overzicht in een html tabel (zie hieronder) met de resultaten (tabel zit in een loop). Later wil ik dit in grafieken steken.

HTML tabel:


<p>Cijfers downloads/landingviews ebooks/wp met ID = <? echo $row['academy_id']; ?> en titel = <?php echo $row['titel']; ?></p>
<table width="50%">
  <tr>
    <td>Anonieme views</td>
    <td><?php echo $row['alvv']; ?></td>
  </tr>
  <tr>
    <td>Anonieme downloads</td>
    <td><?php echo $row['adid']; ?></td>
  </tr>
  <tr>
    <td>Totale conversie</td>
    <td><?php echo round((($row['adid'] / $row['alvv']) * 100), 0); ?>%</td>
  </tr>
</table>

Jan de Laet

12-12-2015 13:30

GROUP BY alv_academy_id, a.titel, c.status?
Dan groepeer je ook op status. Maar ik weet niet zeker of dit het resultaat is wat je wilt.

Note: in principe moet je bij GROUP BY alle velden noemen die geen aggregate functie zijn (zoals SUM of COUNT). Daarom heb ik a.titel ook toegevoegd.

Brecht S

12-12-2015 13:53 gewijzigd op 12-12-2015 13:59

@Jan: bedankt voor je antwoord. De query die ik liet zien is dus niet juist. Ik krijg niet wat ik wil zien.

Het uiteindelijke resultaat zou moeten zijn:


Titel
  - Aantal landingviews met status koud: x
  - Aantal downloads met status koud: x
  - Conversie koud: x%
  - Aantal landingviews zonder status (dit zijn de personen die nog niet voorkomen in de tabel contacten): x
  - Aantal downloads zonder status (...): x
  - Conversie anoniem: x%
  - Aantal landingviews met status lead: x
  - Aantal downloads met status lead: x
  - Conversie lead: x%
  - ... (enz voor alle statussen bij de contacten tabel)

En deze tabel dan in een loop per titel. Zie ook mijn voorbeeld html tabel bovenaan. Dit is de uiteindelijke vorm die ik wil.

Jan de Laet

12-12-2015 14:01

Zoek maar eens op Google onder "pivot table mysql".
Het komt erop neer dat je de count zo vaak laat terugkomen als er status waardes zijn.

Afhankelijk of je status een vaste lijst met waardes is of kan variëren, kun je de query helemaal uitschrijven of moet je hem in je script genereren.

Als je het Google resultaat niet snapt, kom dan terug dan maak ik een voorbeeld.

Brecht S

12-12-2015 14:39

@Jan: ik heb het inderdaad opgezocht en vond deze: http://anothermysqldba.blogspot.de/2013/06/pivot-tables-example-in-mysql.html

Nu weet ik wat je bedoeld maar ik geraak niet uit aan mijn query...
Ik zie nog steeds niet wat ik wil zien

Jan de Laet

12-12-2015 14:47

Op basis van deze post: http://stackoverflow.com/questions/7674786/mysql-pivot-table

zou het zoiets worden:


SELECT 
    a.titel, alv.academy_id, SUM(alv.view) as alvv, 
    SUM(CASE WHEN (c.status='koud') THEN 1 ELSE 0 END) AS SumKoud,
    SUM(CASE WHEN (c.status='lead') THEN 1 ELSE 0 END) AS SumLead,
    SUM(CASE WHEN (c.status='prospect') THEN 1 ELSE 0 END) AS SumProspect,
    SUM(CASE WHEN (c.status='klant') THEN 1 ELSE 0 END) AS SumKlant
FROM 
    academy_landingviews alv
INNER JOIN
    academy_download ad
ON
    alv.academy_id = ad.academy_id
INNER JOIN
    academy a
ON
    alv.academy_id = a.id
INNER JOIN
    contacten c
ON
    alv.user_id = c.id
GROUP BY 
        alv.academy_id, a.titel

Brecht S

12-12-2015 16:40 gewijzigd op 12-12-2015 16:42

@Jan: ik heb even je voorbeeld bekeken en beetje aangepast met de aanpassingen die ik ondertussen al heb gedaan in mijn mysql tabellen:


SELECT 
    a.titel, alv.academy_id, 
    SUM(CASE WHEN (c.cont_status='koud') THEN 1 ELSE 0 END) AS SumKoud,
    SUM(CASE WHEN (c.cont_status='lead') THEN 1 ELSE 0 END) AS SumLead,
    SUM(CASE WHEN (c.cont_status='prospect') THEN 1 ELSE 0 END) AS SumProspect,
    SUM(CASE WHEN (c.cont_status='klant') THEN 1 ELSE 0 END) AS SumKlant,
    SUM(CASE WHEN (c.cont_status='pool') THEN 1 ELSE 0 END) AS SumPool
FROM 
    academy_landingviews alv
INNER JOIN
    academy_download ad
ON
    alv.academy_id = ad.academy_id
INNER JOIN
    academy a
ON
    alv.academy_id = a.id
INNER JOIN
    contacten c
ON
    alv.user_id = c.id
GROUP BY 
    alv.academy_id, a.titel

Maar het resultaat is er nog steeds niet. Ik mis ook nog de download aantallen hier in de query. En mijn landingviews cijfers zijn ook niet juist met hetgeen in manueel tel in de db. Als resultaat krijg ik nu ook maar 1 ebook te zien alhoewel er meerdere in mijn db zitten (11 in totaal). Dus denk dat er nog iets met de JOINS zelf ook zal moeten gebeuren?

Hieronder mijn resultaten tabel:


<table width="100%">
  <tr>
    <td>Landingviews door koud</td>
    <td><?php echo $row['SumKoud']; ?></td>
    <td>Downloads door koud</td>
    <td><?php echo $row['x']; ?></td>
  </tr>
  <tr>
    <td>Landingviews door lead</td>
    <td><?php echo $row['SumLead']; ?></td>
    <td>Downloads door lead</td>
    <td><?php echo $row['x']; ?></td>
  </tr>
  <tr>
    <td>Landingviews door prospect</td>
    <td><?php echo $row['SumProspect']; ?></td>
    <td>Downloads door prospect</td>
    <td><?php echo $row['x']; ?></td>
  </tr>
  <tr>
    <td>Landingviews door klant</td>
    <td><?php echo $row['SumKlant']; ?></td>
    <td>Downloads door klant</td>
    <td><?php echo $row['x']; ?></td>
  </tr>
  <tr>
    <td>Landingviews door pool</td>
    <td><?php echo $row['SumPool']; ?></td>
    <td>Downloads door pool</td>
    <td><?php echo $row['x']; ?></td>
  </tr>
</table>

Hier ontbreken natuurlijk nog de waarden voor de download aantallen.

Jan de Laet

12-12-2015 17:30

Ik hoop dat ik je goed begrijp.
Misschien is het beter om te starten vanuit de academy tabel en die te joinen met subtabellen met de totalen.

Hiermee maak je een totaal query van landings_views per titel en status. Die gebruik ik zo meteen als subquery:


SELECT academy_id, cont_status, count(*) 
FROM academy_landingviews
JOIN contacten ON id=user_id
GROUP BY academy_id, status

Datzelfde doen we voor downloads


SELECT academy_id, cont_status, count(*) 
FROM academy_download
JOIN contacten ON id=user_id
GROUP BY academy_id, status

En dat voegen we dan samen met een lijst van titels en alle statussen, startend vanuit de academy tabel. DE CROSS JOIN kun je eventueel vervangen door een tabel met unieke statussen mocht je die hebben.

 
SELECT 
    id, titel, status
FROM academy
CROSS JOIN (SELECT DISTINCT cont_status FROM contacten)

En nu alles samen:


SELECT 
    id, titel,
    SUM(CASE WHEN (lv.cont_status='koud')     THEN lv.aantal ELSE 0 END) AS SumLvKoud,
    SUM(CASE WHEN (lv.cont_status='lead')     THEN lv.aantal ELSE 0 END) AS SumLvLead,
    SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect,
    SUM(CASE WHEN (lv.cont_status='klant')    THEN lv.aantal ELSE 0 END) AS SumLvKlant,
    SUM(CASE WHEN (lv.cont_status='pool')     THEN lv.aantal ELSE 0 END) AS SumLvPool,
    SUM(CASE WHEN (dl.cont_status='koud')     THEN dl.aantal ELSE 0 END) AS SumDlKoud,
    SUM(CASE WHEN (dl.cont_status='lead')     THEN dl.aantal ELSE 0 END) AS SumDlLead,
    SUM(CASE WHEN (dl.cont_status='prospect') THEN dl.aantal ELSE 0 END) AS SumDlProspect,
    SUM(CASE WHEN (dl.cont_status='klant')    THEN dl.aantal ELSE 0 END) AS SumDlKlant,
    SUM(CASE WHEN (dl.cont_status='pool')     THEN dl.aantal ELSE 0 END) AS SumDlPool
FROM academy
CROSS JOIN (SELECT DISTINCT cont_status FROM contacten)

LEFT OUTER JOIN 
    (SELECT academy_id, cont_status, count(*) as aantal
     FROM academy_landingviews
     JOIN contacten ON id=user_id
     GROUP BY academy_id, status) AS lv
   ON academy.id=lv.academy_id and contacten.cont_status=lv.cont_status
LEFT OUTER JOIN
   (SELECT academy_id, cont_status, count(*) as aantal
     FROM academy_download
     JOIN contacten ON id=user_id
     GROUP BY academy_id, status) AS dl
   ON academy.id=dl.academy_id and contacten.cont_status=dl.cont_status
GROUP BY id, titel

Brecht S

12-12-2015 17:39 gewijzigd op 12-12-2015 17:42

@Jan: als ik letterlijk jouw voorbeeld even probeer dan krijg ik een foutmelding: Every derived table must have its own alias
Ik heb geen idee wat ze hiermee willen zeggen?

en ik zie bvb ook dl.cont_status en lv.cont_status staan. Maar de cont_status is de lead, koud, ... en die komt enkel maar voor in de tabel contacten.

Jan de Laet

12-12-2015 17:59

Ah, ik denk dat er nog een alias ontbreekt (AS) bij een van de subqueries (SELECT DISTINCT...). Ik heb het dan ook uit de losse pols uitgetikt, want ik heb jouw tabellen natuurlijk niet.


SELECT 
    id, titel,
    SUM(CASE WHEN (lv.cont_status='koud')     THEN lv.aantal ELSE 0 END) AS SumLvKoud,
    SUM(CASE WHEN (lv.cont_status='lead')     THEN lv.aantal ELSE 0 END) AS SumLvLead,
    SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect,
    SUM(CASE WHEN (lv.cont_status='klant')    THEN lv.aantal ELSE 0 END) AS SumLvKlant,
    SUM(CASE WHEN (lv.cont_status='pool')     THEN lv.aantal ELSE 0 END) AS SumLvPool,
    SUM(CASE WHEN (dl.cont_status='koud')     THEN dl.aantal ELSE 0 END) AS SumDlKoud,
    SUM(CASE WHEN (dl.cont_status='lead')     THEN dl.aantal ELSE 0 END) AS SumDlLead,
    SUM(CASE WHEN (dl.cont_status='prospect') THEN dl.aantal ELSE 0 END) AS SumDlProspect,
    SUM(CASE WHEN (dl.cont_status='klant')    THEN dl.aantal ELSE 0 END) AS SumDlKlant,
    SUM(CASE WHEN (dl.cont_status='pool')     THEN dl.aantal ELSE 0 END) AS SumDlPool
FROM academy
CROSS JOIN (SELECT DISTINCT cont_status FROM contacten) AS c

LEFT OUTER JOIN 
    (SELECT academy_id, cont_status, count(*) as aantal
     FROM academy_landingviews
     JOIN contacten ON id=user_id
     GROUP BY academy_id, status) AS lv
   ON academy.id=lv.academy_id and c.cont_status=lv.cont_status
LEFT OUTER JOIN
   (SELECT academy_id, cont_status, count(*) as aantal
     FROM academy_download
     JOIN contacten ON id=user_id
     GROUP BY academy_id, status) AS dl
   ON academy.id=dl.academy_id and c.cont_status=dl.cont_status
GROUP BY id, titel

de lv_cont_status komt ook uit contacten, ik doe daarvoor de JOIN contacten in de subquery.

Probeer de queries uit mijn vorige post maar eens stap voor stap uit te voeren in bv phpMyAdmin. Dan zie je wat het resultaat telkens is.

Brecht S

12-12-2015 18:28 gewijzigd op 12-12-2015 18:36

@Jan: we komen in de buurt. Ik heb de query lichtjes gewijzigd want er waren nog een paar foutjes. Ik krijg nu een overzicht met aantallen hier en daar. Moet wel nog eens controleren of de aantallen juist zijn overal.
Maar er is nog 1 probleem. Ik heb soms geen user_id in de tabellen en die worden nu niet weergegeven. Enkel de resultaten die ook een user_id hebben worden nu weergegeven.
Het zou kunnen dat iemand een landingview doet maar niet gekend is in de tabel contacten, dan is dit dus een anonieme view. Bij de downloads kan dit niet, want iedereen die een download doet krijgt ook een user_id (als ze er nog geen hadden). Het gaat hier enkel over de landingviews.

Aangepaste query:


SELECT 
    id, titel,
    SUM(CASE WHEN (lv.cont_status='koud')     THEN lv.aantal ELSE 0 END) AS SumLvKoud,
    SUM(CASE WHEN (lv.cont_status='lead')     THEN lv.aantal ELSE 0 END) AS SumLvLead,
    SUM(CASE WHEN (lv.cont_status='prospect') THEN lv.aantal ELSE 0 END) AS SumLvProspect,
    SUM(CASE WHEN (lv.cont_status='klant')    THEN lv.aantal ELSE 0 END) AS SumLvKlant,
    SUM(CASE WHEN (lv.cont_status='pool')     THEN lv.aantal ELSE 0 END) AS SumLvPool,
    SUM(CASE WHEN (dl.cont_status='koud')     THEN dl.aantal ELSE 0 END) AS SumDlKoud,
    SUM(CASE WHEN (dl.cont_status='lead')     THEN dl.aantal ELSE 0 END) AS SumDlLead,
    SUM(CASE WHEN (dl.cont_status='prospect') THEN dl.aantal ELSE 0 END) AS SumDlProspect,
    SUM(CASE WHEN (dl.cont_status='klant')    THEN dl.aantal ELSE 0 END) AS SumDlKlant,
    SUM(CASE WHEN (dl.cont_status='pool')     THEN dl.aantal ELSE 0 END) AS SumDlPool
FROM academy
CROSS JOIN (SELECT DISTINCT cont_status FROM contacten) AS c

LEFT OUTER JOIN 
    (SELECT academy_id, cont_status, count(*) as aantal
     FROM academy_landingviews alv
     JOIN contacten c ON c.id=alv.user_id
     GROUP BY academy_id, cont_status) AS lv
   ON academy.id=lv.academy_id and c.cont_status=lv.cont_status
LEFT OUTER JOIN
   (SELECT academy_id, cont_status, count(*) as aantal
     FROM academy_download adl
     JOIN contacten c ON c.id=adl.user_id
     GROUP BY academy_id, cont_status) AS dl
   ON academy.id=dl.academy_id and c.cont_status=dl.cont_status
GROUP BY id, titel

En de bijhorende HTML tabel (die in een loop zit):


<table width="70%">
  <tr>
    <td>Landingviews door anoniem</td>
    <td><?php echo $row['x']; ?></td>
    <td>&nbsp;</td>
    <td>&nbsp;</td>
  </tr>
  <tr>
    <td>Landingviews door koud</td>
    <td><?php echo $row['SumLvKoud']; ?></td>
    <td>Downloads door koud</td>
    <td><?php echo $row['SumDlKoud']; ?></td>
  </tr>
  <tr>
    <td>Landingviews door lead</td>
    <td><?php echo $row['SumLvLead']; ?></td>
    <td>Downloads door lead</td>
    <td><?php echo $row['SumDlLead']; ?></td>
  </tr>
  <tr>
    <td>Landingviews door prospect</td>
    <td><?php echo $row['SumLvProspect']; ?></td>
    <td>Downloads door prospect</td>
    <td><?php echo $row['SumDlProspect']; ?></td>
  </tr>
  <tr>
    <td>Landingviews door klant</td>
    <td><?php echo $row['SumLvKlant']; ?></td>
    <td>Downloads door klant</td>
    <td><?php echo $row['SumDlKlant']; ?></td>
  </tr>
  <tr>
    <td>Landingviews door pool</td>
    <td><?php echo $row['SumLvPool']; ?></td>
    <td>Downloads door pool</td>
    <td><?php echo $row['SumDlPool']; ?></td>
  </tr>
</table>

Dus hier zit ik nog met de x die nog een cijfer moet krijgen

Reageren

Inloggen om te reageren