[mysql] sorteren op count()

[TwoR]

Hoi, ik heb de voglende tabel :

code:

CREATE TABLE `ck_koppel_toewijzing` (
  `id` int(255) NOT NULL auto_increment,
  `user_id1` int(255) NOT NULL default '0',
  `user_id2` int(255) NOT NULL default '0',
  `vaardigheid_id` int(255) NOT NULL default '0',
  `antwoord` varchar(255) NOT NULL default '',
  `competentie_id` int(255) NOT NULL default '0',
  `definitief` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM;

Nu wil ik kijken hoevaak een vaardigheid_id voorkomt en dit dan sorteren met het hoogte aantal boven aan (DESC). Maar dit lukt me dus niet. Op dit moment heb ik het voglende:

PHP:

$sql_resultaat = "SELECT DISTINCT vaardigheid_id FROM ck_koppel_toewijzing 
  WHERE competentie_id = '$_SESSION[session_user_id]' AND definitief = '1'";
  $query_resultaat = mysql_query($sql_resultaat) or die(mysql_error());
  
  while($resultaat = mysql_fetch_array($query_resultaat)){
  
    $sql_aantal = "SELECT count(*) AS aantal FROM ck_koppel_toewijzing
    WHERE vaardigheid_id = '$resultaat[vaardigheid_id]' ORDER BY aantal DESC";
    $query_aantal = mysql_query($sql_aantal) or die(mysql_error()); 
    $aantal = mysql_result($query_aantal,0); 
    echo "[ $resultaat[vaardigheid_id] ] aantal = $aantal <BR>";
}

Ik weet dat dit bij lange na niet goed is maar hoe dan wel ik heb al hier op GoT lopen zoeken naar "sorteren op count" en "order by count" en dan krijg je heel veel resultaten terug maar ik kan gewoon niet vinden wat ik zoek.

Het is dus de bedoeling dat je er op het eind zoiets uit krijt per vaardigheid _id het aantal gesorteerd op aantal.

code:

vaardigheid_id   aantal
4                 4
8                 3
1                 2
9                 2
2                 1

Wie kan mij een helpende hand bieden want ik wordt er ondertussen echt bedroeft van ben er namelijk al best heel lang mee bezig.

[ Voor 9% gewijzigd door TwoR op 15-04-2004 11:25 ]

[whoami]

Waarom wil je zo moeilijk doen?
Je kan dit met 1 sql statement verkrijgen. Kijk eens naar COUNT, group by, etc....

[P_de_B]

Direct in je SQL code:

code:

SELECT vaardigheid_id, count(vaardigheid_id) as Aantal 
FROM Tabel GROUP BY vaardigheid_id ORDER BY count(vaardigheid_id)

[cameodski]

Zoiets misschien?

code:

SELECT vaardigheid_id, count(*) as aantal
FROM ck_koppel_toewijzing
GROUP BY vaardigheid_id
ORDER BY count(*) DESC

[_-= Erikje =-_]

Wat je wilt is alle rijen met dezelfde vaardigheid_id groeperen tot 1 groep en daarbinnen het aantal rijen bekijken.

Dit kan met 1 query

SQL:

SELECT      vaardigheid_id, 
                  count(*) AS aantal 
FROM         ck_koppel_toewijzing
GROUP BY  vaardigheid_id
ORDER BY  aantal DESC

[edit]
sneller tiepen en geen uitleg willen verzinnen, anders spuit 11 mode )

[ Voor 12% gewijzigd door _-= Erikje =-_ op 15-04-2004 11:35 ]

[TwoR]

PHP:

$sql = "SELECT vaardigheid_id, count(vaardigheid_id) as aantal 
          FROM ck_koppel_toewijzing  
          WHERE competentie_id = '$_SESSION[session_user_id]' AND definitief = '1'
          GROUP BY vaardigheid_id 
          ORDER BY count(vaardigheid_id)";
  $query = mysql_query($sql) or die(mysql_error());

Ik heb het nu zo, Dit had ik ook al eens geprobeerd alleen dan krijg je een foutmelding "Invalid use of group function"

[Verwijderd]

je moet wel opletten dat als je sorteert op COUNT of MAX etc je wel ` moet gebruiken
voorbeeldje:

code:

SELECT vaardigheid_id, count(vaardigheid_id)  FROM Tabel GROUP BY vaardigheid_id ORDER BY `count(vaardigheid_id)`

of

code:

SELECT vaardigheid_id, count(vaardigheid_id) AS aantal  FROM Tabel GROUP BY vaardigheid_id ORDER BY aantal

dat moet volgens mij ook werken

[TwoR]

oke maar hoe komt het nou dat ie het niet goed sorteert ik krijg nu het volgende rijtje terug:
Tussen de [ haken ] is het vaardigheids id nummer en achter de = is het aantal maar zoals je ziet niet goed gesorteerd.

code:

[ 4 ] aantal = 2 
[ 5 ] aantal = 2 
[ 6 ] aantal = 2 
[ 11 ] aantal = 2 
[ 15 ] aantal = 2 
[ 25 ] aantal = 1 
[ 7 ] aantal = 2 
[ 16 ] aantal = 2 
[ 30 ] aantal = 1 
[ 13 ] aantal = 2 
[ 19 ] aantal = 1 
[ 27 ] aantal = 1 
[ 29 ] aantal = 1 
[ 9 ] aantal = 1 
[ 26 ] aantal = 1 
[ 20 ] aantal = 1 
[ 18 ] aantal = 1 
[ 22 ] aantal = 1 
[ 23 ] aantal = 1

[whoami]

Er is gesorteerd op aantal, als je op id wilt sorteren moet je je order by clause aanpassen.

[cameodski]

Dat is toch goed? Het aantal ziet er tenminste gesorteerd uit.

[TwoR]

nee het moet worden gesorteerd worden op aantal maar zoals je ziet is dat niet gebeurd hoe kan op regel 6 dan het aantal 1 zijn en daaronde weer 2

[cameodski]

Idd, je hebt gelijk. Welke query heb je nu eigenlijk gebruikt?

[TwoR]

code:

$sql = "SELECT vaardigheid_id, count(vaardigheid_id) as aantal 
          FROM ck_koppel_toewijzing  
          WHERE competentie_id = '$_SESSION[session_user_id]' AND definitief = '1'
          GROUP BY vaardigheid_id 
          ORDER BY 'count(vaardigheid_id)' DESC";
  $query = mysql_query($sql) or die(mysql_error());
  $aantal = mysql_result($query, 0);

Deze

[ Voor 8% gewijzigd door TwoR op 15-04-2004 12:25 ]

[P_de_B]

Misschien is dat een mysql iets, maar waarom moet je single quotes om de count() in het ORDER BY statement zetten? Het lijk me dat hij daarom niet sorteert op de werkelijke count, maar de string.

[TwoR]

oke het is gelukt maar ik heb nu toch weer 2 select statements
Dit is het nu geworden

PHP:

$sql = "SELECT vaardigheid_id, count(vaardigheid_id) AS aantal  
  FROM ck_koppel_toewijzing   
  GROUP BY vaardigheid_id 
  ORDER BY aantal DESC
  LIMIT 12";
 $query = mysql_query($sql) or die(mysql_error());
 
  while($resultaat = mysql_fetch_array($query)){
    
    $sql_aantal = "SELECT count(*) AS aantal FROM ck_koppel_toewijzing
    WHERE vaardigheid_id = '$resultaat[vaardigheid_id]'";
    $query_aantal = mysql_query($sql_aantal) or die(mysql_error()); 
    $aantal = mysql_result($query_aantal,0); 
    echo "[ $resultaat[vaardigheid_id] ] aantal = $aantal <BR>";
  }

Het rijtje dat er nu uitkomt:

code:

[ 4 ] aantal = 2 
[ 11 ] aantal = 2 
[ 15 ] aantal = 2 
[ 7 ] aantal = 2 
[ 13 ] aantal = 2 
[ 10 ] aantal = 1 
[ 25 ] aantal = 1 
[ 3 ] aantal = 1 
[ 5 ] aantal = 1 
[ 12 ] aantal = 1 
[ 14 ] aantal = 1 
[ 16 ] aantal = 1

[ Voor 21% gewijzigd door TwoR op 15-04-2004 12:33 ]

[P_de_B]

TwoR schreef op 15 april 2004 @ 12:33:
oke het is gelukt maar ik heb nu toch weer 2 select statements
Dit is het nu geworden

PHP:

$sql = "SELECT vaardigheid_id, count(vaardigheid_id) AS aantal  
  FROM ck_koppel_toewijzing   
  GROUP BY vaardigheid_id 
  ORDER BY aantal DESC
  LIMIT 12";
 $query = mysql_query($sql) or die(mysql_error());
 
  while($resultaat = mysql_fetch_array($query)){
    
    $sql_aantal = "SELECT count(*) AS aantal FROM ck_koppel_toewijzing
    WHERE vaardigheid_id = '$resultaat[vaardigheid_id]'";
    $query_aantal = mysql_query($sql_aantal) or die(mysql_error()); 
    $aantal = mysql_result($query_aantal,0); 
    echo "[ $resultaat[vaardigheid_id] ] aantal = $aantal <BR>";
  }

Ik ben blij dat je tevreden bent, maar dit is natuurlijk niet een goede oplossing. Je moet als het kan een sql query in een loop vermijden.

[TwoR]

Dat weet ik, maar aan de andere kant ben ik blij dat het in iedergeval werkt. Ik ga nog wel even uitzoeken hoe ik dit alles nou netter kan krijgen.

[P_de_B]

Komt het nou niet van die quotes? Als je die weghaalt, krijg je dan weer die foutmelding die je eerder gaf?

MySQL kan toch wel omgaan met aggregates in de order by clausule?

[Verwijderd]

TwoR schreef op 15 april 2004 @ 12:24:

code:

$sql = "SELECT vaardigheid_id, count(vaardigheid_id) as aantal 
          FROM ck_koppel_toewijzing  
          WHERE competentie_id = '$_SESSION[session_user_id]' AND definitief = '1'
          GROUP BY vaardigheid_id 
          ORDER BY 'count(vaardigheid_id)' DESC";
  $query = mysql_query($sql) or die(mysql_error());
  $aantal = mysql_result($query, 0);

Deze

als je mijn post goed had gelezen had je gezien dat er geen single quote stond maar zo'n ding links naast de 1 op je toetsenbord (`), en dan werkt het wel

[P_de_B]

Verwijderd schreef op 15 april 2004 @ 12:53:
[...]


als je mijn post goed had gelezen had je gezien dat er geen single quote stond maar zo'n ding links naast de 1 op je toetsenbord (`), en dan werkt het wel

offtopic:
wat doen zo'n ding dan? Is dat verplicht voor MySQL?

[TwoR]

Oke nu dit werkt wil ik graag aan de 'experts' nog een vraagje stellen.
Zoals in de tabel (ts) te zien is is er ook een kolom antwoord deze kan bestaan uit 3 verschillende basis,ervaren of excellent nu wil ik dat de 12 vaardigheid_id's die ik ondertussen heb worden verdeelt over deze 3 antwoorden maar dan moeten ze dus bij het antwoord komen te staan die het meest voorkomt. Ik hoop dat iemand dit snapt want het is lastig te omschrijven.

[ripexx]

P_de_B schreef op 15 april 2004 @ 12:54:
[...]

offtopic:
wat doen zo'n ding dan? Is dat verplicht voor MySQL?

Eenvoudiger is gewoon een alias gebruiken Dus count(col) AS aantal en dan ORDER BY aantal

Veel eenvoudiger en voorkomt problemen met '," en `

[TwoR]

ripexx schreef op 15 april 2004 @ 13:31:
[...]
Eenvoudiger is gewoon een alias gebruiken Dus count(col) AS aantal en dan ORDER BY aantal
Veel eenvoudiger en voorkomt problemen met '," en `

Zoals te zien in mijn post hoe ik het uiteindelijk had zie je ook dat ik dat heb. Het voorkomt inderdaad het probleem met de quotjes ' '

[P_de_B]

TwoR schreef op 15 april 2004 @ 13:30:
Oke nu dit werkt wil ik graag aan de 'experts' nog een vraagje stellen.
Zoals in de tabel (ts) te zien is is er ook een kolom antwoord deze kan bestaan uit 3 verschillende basis,ervaren of excellent nu wil ik dat de 12 vaardigheid_id's die ik ondertussen heb worden verdeelt over deze 3 antwoorden maar dan moeten ze dus bij het antwoord komen te staan die het meest voorkomt. Ik hoop dat iemand dit snapt want het is lastig te omschrijven.

Dit is met GROUP BY wel op te lossen. Ik kan je aanraden eens een SQL Tutorial over group by door te nemen. Volgens mij staat in de FAQ van Programming & Webscripting ook een uitleg.

[TwoR]

goup by d'oh dat ik daar zelf niet even opkom

Ik zal eens even wat prutsen.

[Verwijderd]

Het antwoord op jouw kwestie is al lang aangedragen! Zie onderstaand:

_-= Erikje =-_ schreef op 15 april 2004 @ 11:34:

SQL:

SELECT    vaardigheid_id,
          count(*) AS aantal
FROM      ck_koppel_toewijzing
GROUP BY  vaardigheid_id
ORDER BY  aantal DESC

[TwoR]

oke nog even over me eerste probleem:
als ik dit doe :

code:

$sql = "SELECT vaardigheid_id, count(vaardigheid_id) AS aantal  
  FROM ck_koppel_toewijzing   
  GROUP BY vaardigheid_id 
  ORDER BY aantal DESC
  LIMIT 12";
 $query = mysql_query($sql) or die(mysql_error());

dan sorteert hij goed maar als ik er ook nog een WHERE ingooi op de volgende manier:

code:

$sql = "SELECT vaardigheid_id, count(vaardigheid_id) AS aantal  
  FROM ck_koppel_toewijzing   
  WHERE definitief = '1' AND competentie_id  = '$_SESSION[session_user_id]'
  GROUP BY vaardigheid_id 
  ORDER BY aantal DESC
  LIMIT 12";
 $query = mysql_query($sql) or die(mysql_error());

Dan is de sorteer volgorder ineens niet meer goed. Is dit nu een bug in MySQL of doe ik echt iets verkeerd?

Het zit hem hier trouwens in definitief = '1' Als die erbij staat wordt er ineens niet meer goed gesorteerd. Als ik ipv AND definitief = '1' OR gebruik doet ie het ineens ook weer goed. Maar volgens mij moet her toch echt AND staan.

[ Voor 20% gewijzigd door TwoR op 15-04-2004 13:58 ]

[ripexx]

TwoR schreef op 15 april 2004 @ 13:54:

code:

$sql = "SELECT vaardigheid_id, count(vaardigheid_id) AS aantal  
  FROM ck_koppel_toewijzing   
  WHERE definitief = 1 AND competentie_id  = ".$_SESSION[session_user_id]."
  GROUP BY vaardigheid_id 
  ORDER BY aantal DESC
  LIMIT 12;";
 $query = mysql_query($sql) or die(mysql_error());

Even wat kleine dingen aangepast, je waarden van definitie en competentie_id zijn integers en worden dus gebruikt zonder quotes. De WHERE is in principe gewoon goed dus daar ligt het niet aan.

[TwoR]

De quotes maken niks uit, de aantallen blijven verkeerd gesorteerd. Ik snap er ondertussen echt helemaal niks meer van

[kokorico]

Ik zit met hetzelfde probleem als jou ...
Weet iemand opllossing?

[ Voor 71% gewijzigd door kokorico op 13-11-2004 22:46 ]