[PHP/SQL] resultaat group by in kolom

[Daggie]

Ik zit met volgende vraag. Ik probeer het tot de essentie te houden.

Stel een tabel met 4 kolommen: lid, jaartal, cijfer, leverancier.
Elk lid heeft meerdere (cijfer)records, per jaartal, per leverancier.
Vb:

Lid	Jaartal	Cijfer	Leverancier
Lid 1	2007	100	Leverancier 1
Lid 1	2007	100	Leverancier 2
Lid 1	2007	300	Leverancier 3
Lid 2	2007	100	Leverancier 1
...	...	...	...
Lid 1	2006	50	Leverancier 1
...	...	...	...

Ik wil een tabel als resultaat met het cijfer per jaar, per lid, voor alle leveranciers. Dus het resultaat dat ik wil, ziet er zo uit:

Jaartal 2007	Jaartal 2006	Jaartal 2005	Cijfers Lid
300	200	100	Lid 1
350	250	150	Lid 2

Ik kan de cijfers querien met

SELECT lid, jaar, sum(cijfer)
FROM cijfers
where jaar > 2002
GROUP BY lid, jaar
order by jaar, lid
Maar dan krijg ik een record, per lid, per jaar:

Lid	Jaar	Cijfer
Lid 1	2007	300
Lid 2	2007	350
...	...	...
Lid 1	2006	200
Lid 2	2006	250

Iemand een idee hoe ik van mijn resultaat (onderste tabel) iets render in de vorm van de middenste tabel? Het moet in PHP en de database draait op MYSQL 4.1.21.

[Johnny]

Met alleen SQL gaat het niet lukken. De indeling van je database lijkt me ook niet optimaal, maar dat is een ander verhaal.

Wat je moet doen is je resultset eerst omzetten naar een datastructuur die je voor de uitvoer kan gebruiken.

Zoiets dus:

PHP:

$members = array();

$query = "SELECT lid, jaar, sum(cijfer) AS cijfer FROM cijfers where jaar > 2002 GROUP BY lid, jaar order by jaar, lid";
$result = mysql_query($query);
while($cijfer = mysql_fetch_assoc($result)) {

//maak een lijst aan voor dit lid als die er nog niet is
if(!is_array($members[$cijfer['lid']])) {
$members[$cijfer['lid']] = array();
}

//voeg cijfer toe aan de lijst van dit lid met het jaar als de key
$members[$cijfer['lid']][$cijfer['jaar']] = $cijfer['cijfer'];
}

//laat de datastructuur zien op het scherm, gewoon om te zien hoe hij in elkaar zit
print_r($members);

echo('<table>');

//doorloop de lijst van leden en zet de lijst met jaren in de variable $years
foreach($members as $member => $years) {

echo('<tr><td>'.$years[2007].'</td><td>'.$years[2006].'</td><td>'.$years[2005].'</td><td>'.$member.'</td></tr>');
}

echo('</table>');

Er moet dan wel voor ieder lid ieder jaar een cijfer zijn anders krijg je een dikke vette error.

[ Voor 14% gewijzigd door Johnny op 10-12-2007 22:21 ]

[Daggie]

Ik heb het even geprobeerd met de volledige berekening en het klopt perfect Johnny !

Je bent geweldig bedankt.

(de tabelstructuur was oorspronkelijk niet bedoelt om dit soort rapporten mee te trekken .. wat mijn goedkoop excuus is ...)

Ben echt blij met de oplossing, nog eens bedankt.

[Goodielover]

Je kan het ook met één select statement doen als je SQL-taal een soort van if statement ondersteunt.

code:

select lid
      ,sum( if(jaartal=2007,cijfer,0)) cijfer2007
      ,sum( if(jaartal=2006,cijfer,0)) cijfer2006
      ,sum( if(jaartal=2005,cijfer,0)) cijfer2005
      ,sum( if(jaartal=2005,cijfer,0)) cijfer2004
      ,sum( if(jaartal=2005,cijfer,0)) cijfer2003
FROM   cijfers
where  jaar > 2002
GROUP BY lid
order by lid