[php/mysql]query met sum, group by en order

[nikao]

ik heb een tabel waarin ik zoekmachines opsla en daarbij met welke query gezocht is + een aantal.

als er met eenzelfde zoekquery gezocht is hoogt ie gewoon dat aantal op.. zo niet.. dan voeg ik een nieuwe rij toe.

nu wil ik dus een overzichtje van zoekmachines maken met daarbij het percentage hoe vaak die gebruikt is.

nu dacht ik met sum(aantal) het totaal van raadplegingen op te halen maar die blijkt 0 te zijn. (krijg een division bij zero error bij de percentage berekening.

mijn query ziet er als volgt uit.

PHP:

$sql = "SELECT sum(aantal) as total, $column as naam, aantal 
FROM $table where site = '$site' 
GROUP BY $column, aantal 
ORDER BY aantal DESC $limit";

het aantal en de naam krijg ik wel goed terug...
iemand enig id waarom de sum(aantal) niet lijkt te werken?

[ Voor 3% gewijzigd door nikao op 25-06-2003 15:01 ]

[whoami]

Heb je voor sommige records misschien null values in de column aantal zitten?

Trouwens.... Ik denk dat jouw query niet helemaal het gewenste resultaat zal opleveren...

[nikao]

nee .. nergens null...

en hij haalt ook niet alle rows eruit zie ik nu..

[DeverauX]

Controleer de waarde van de variabele die je gebruikt ($site) eens door de waarde naar het scherm te sturen. (Doe dit trouwens ook met je query)

[nikao]

ok ..

code:

SELECT sum(aantal) as total, search_engine as naam, aantal 
FROM spiderstat_search_engine where site = '3' 
GROUP BY search_engine, aantal 
ORDER BY aantal DESC LIMIT 0, 25

dit geeft dus niet het gewenste resultaat.. als ik dit in phpmyadmin paste dan krijg ik wel waardes voor total (waarom ik dan een division bij zero krijg in mijn script mag joost weten...) .. maar ook daar niet het gewenste resultaat.. (lijkt alleen de laatste 3 rows te pakken)

[ Voor 3% gewijzigd door nikao op 25-06-2003 15:01 . Reden: even topic leesbaar houden :) ]

[marty]

post je hele code eens...

[DeverauX]

(waarom ik dan een division bij zero krijg in mijn script mag joost weten...)

Ik neem aan dat je "total" deelt door "aantal" om het percentage te berekenen? In dit geval zou je dus de waarde van "aantal" even moeten controleren

[nikao]

uhm.. nee.... aantal delen door total x 100 = percentage

dit is de functie:

PHP:

function ShowStats_search($table,$column, $desc, $limit = "", $site) { 
      global $db; 
      $limit = ($limit) ? "LIMIT 0, $limit" : ""; 
       
      /* Maak tabel-kop */ 
      echo "<table border=1 cellspacing=0 width=600>\n"; 
      echo "<tr bgcolor=\"black\">\n"; 
      echo "\t<td colspan=\"3\"><font color=\"white\" size=\"2\" face=\"verdana\"><b>&nbsp;$desc</b></font></td>\n"; 
      echo "</tr>\n"; 
       
      /* Inhoud - lees uit database */ 
      $sql = "
       SELECT sum(aantal) as total, $column as naam, aantal 
       FROM $table where site = '$site' 
       GROUP BY $column, aantal 
       ORDER BY aantal DESC $limit
        "; 
      //echo "$sql";
      
      $result = mysql_query($sql) or die(mysql_error()); 
      $first = TRUE; 
      while ($row = mysql_fetch_object($result)) 
      { 
         if ($first) 
         { 
            $max = $row->aantal; 
            $first = FALSE; 
         }
          
         $width = round($row->aantal / $max * 200); 
         $percentage = ($row->aantal / $total) * 100;
         $percentage = number_format($percentage,2);
         echo "<tr>\n"; 
         echo "<td width=200><font size=\"1\" face=\"verdana\">$row->naam $row->aantal</td>\n"; 
         echo "<td width=50><font size=\"1\" face=\"verdana\">$percentage %</td>\n"; 
         echo "<td width=350 valign=\"middle\"><img src=\"img/graph.gif\" height=15 width=$width 
            title=\"$row->naam: $row->aantal\"></td>\n"; 
         echo "</tr>\n"; 
      } 
      
      /* Sluit tabel */ 
      echo "</table><p>\n"; 
   }

en die wordt zo aangeroepen:

PHP:

ShowStats_search("spiderstat_search_engine","search_engine", "Zoekmachines", 25, $site);

en ik weet dat dit nog niet precies doet wat ik beschreef.. maar ik wilde eerst die percentages fixen.. en daarna maken dat ie de search_engines niet dubbel liet zien (steeds met andere query)

[DeverauX]

uhm.. nee.... aantal delen door total x 100 = percentage

Ja oke dat bedoel ik. Maar als jij een division by zero melding krijgt moet je gewoon even de waardes die je in de deling gebruikt naar het scherm sturen. Kan je meteen zien wat er aan de hand is (Oftewel debuggen)

[nikao]

ik krijg dan dus van de laatste 3 rijen het aantal / *leeg*
nix dus voor het total..

de tabel is deze:

code:

5 Google 2   3 
9 Yahoo 1   3 
8 Google 2 pokpok 3 
10 Google 2 wackass 3 
11 Google 1 xiiin 3 
12 Yahoo 1 Desktopgirls 3

en hij retouneerd dus alleen de laatste 3.. en de aantallen wel ok..

[nikao]

ben wellicht wat ongeduldig.. maar is er iemand die me kan uitleggen hoe de query werkt zoals die nu is zodat ik snap waarom ik het resultaat krijg wat ik nu krijg.. en wellicht begrijp wat er anders moet

[justmental]

code:

       SELECT sum(aantal) as total, $column as naam, aantal 
       FROM $table where site = '$site' 
       GROUP BY $column, aantal 
       ORDER BY aantal DESC $limit

Je groepeert op de naam en het aantal, alles over de combinatie van deze kun je aggregeren met bv. een sum.
Je doet echter je sum over aantal, wat in je group by zit.

[nikao]

ik hjebn nu alleen group by $column staan.. en ik krijg nu wel alleen maar google en 1 keer yahoo.. maar nog geen total ..

[whoami]

Je moet groeperen op alle velden die in je select list staan en geen aggregated function zijn.
In jouw voorbeeld moet je dus groeperen op naam en aantal.

Wat gaat die query nu doen:
Hij gaat kijken hoeveel keer iedere combinatie naam/aantal voorkomt.

Geef eens wat uitleg bij je table. Welke velden zijn dat? Wat stellen die cijfertjes voor?
Die eerste kolom is de PK veronderstel ik, en de rest?

[nikao]

de tabel bestaat uit deze kolommen

code:

ID     search_engine     aantal    keywords    site

[whoami]

Waarom sla je eigenlijk dat aantal op?
Het is gewoon een calculated field dat je anders kunt bekomen dmv een count.

Als je enkel wilt weten hoe vaak een search engine gebruikt is, dan heb je toch die keywords niet nodig om dat te bepalen?

code:

select sum(aantal), search_engine
from tabel
group by search_engine

zou al moeten weergeven hoe vaak searchengine Google, Yahoo, etc... gebruikt werd.

Om dat in een percentage te gieten heb je het totaal aantal verschillende search engines nodig:

code:

select count (distinct searchengine) from tabel

Dan doe je:

code:

google_aantal  / totaal_aantal * 100

[nikao]

ik sla dat op deze manier op.. omdat ik zo ook kan bijhouden hoe vaak een bepaald keyword bij die zoekmachine is gebruikt.. dat sla ik dus in de column aantal op..

maar je komt er dus niet onderuit om het in 2 queries te gooien?
het totaal aantal is dus column 'aantal' van alle rows bij elkaar opgeteld..

[whoami]

nikao schreef op 25 June 2003 @ 21:47:
ik sla dat op deze manier op.. omdat ik zo ook kan bijhouden hoe vaak een bepaald keyword bij die zoekmachine is gebruikt.. dat sla ik dus in de column aantal op..

Slecht design. Ook dat kan je eruit halen zonder gebruik te maken van een extra (calculated field).

[nikao]

ja maar anders moet ik dus iedere keer een nieuwe row toevoegen.. en dan rows tellen met dat keyword en die zoekmachine.?.. is dat dan beter/sneller ?

[whoami]

nikao schreef op 25 June 2003 @ 22:36:
ja maar anders moet ik dus iedere keer een nieuwe row toevoegen.. en dan rows tellen met dat keyword en die zoekmachine.?.. is dat dan beter/sneller ?

Nu voeg je idd altijd een row toe, of ga je die row gaan updaten.
Dat is eigenlijk een beetje 'error-prone' vind ik.

Les 1 in normaliseren is dan ook altijd dat je calculated fields niet mag opslaan. Momenteel moet ik op m'n werk op een databank werken waar er dus wel van die calculated fields in staan, en het is een nachtmerrie.

[nikao]

doh! . gelukkig zag niemand het.. maar ik riep $total aan.. in plaats van $row->total;

maar nu heb ik dus het aantal per searchengine opgeteld.. kan ik in diezelfde query ook een compleet totaal krijgen?.. of zal ik toch 2 queries moeten gaan gebruiken?