[mysql]Een count op datum, ook een result bij 0

[Tijsje]

Ik ben bezig met een eigen stats script, alle bezoeken aan mijn site sta ik op in een mysql database.
Nu wil ik een query uitvoeren die me als result het aantal bezoekers per dag geeft.
Ik heb deze query gemaakt:

code:

SELECT DATE_FORMAT(stamp, "%d-%m-%Y") as datum, COUNT(stamp) as aantal FROM bezoeken  GROUP BY datum

Maar als er op een dag geen bezoekers zijn geweest, dan laat hij heel de datum weg in het result.
Ik heb liever dat hij hier de datum neerzet met gewoon een 0 erachter.
Ik heb er al in php omheen zitten werken maar dit is eigenlijk ook geen oplossing.
Is dit ook op te lossen met een mysql query?

[Verwijderd]

Dan gebruik je toch gewoon NOW()?

En nog een IF eromheen die checkt of het aantal nul is natuurlijk

Zoiets:

code:

CASE Count(stamp)
    WHEN 0 THEN NOW()
    ELSE DATE_FORMAT(..)
END AS datum

[ Voor 96% gewijzigd door Verwijderd op 25-11-2005 16:09 ]

[P_de_B]

Een tabel maken met alle dagen van het jaar erin, daaruit selecteren en dan een left join met deze tabel is denk ik de makkelijkste oplossing. Zoiets:

SELECT datumtabelletjedatum, count(bezoekers.*)
FROM datumtabelletje
LEFT OUTER JOIN bezoekers ON datumtabelletje.datum = bezoekers.datum
GROUP BY datumtabelletje.datum

Eventueel moet je nog een ISNULL gebruiken om een 0 terug te geven als een record niet bestaat in de bezoekers tabel maar ik weet even niet het MySQL equivalent van deze SQL Server functie.

[Verwijderd]

P_de_B schreef op vrijdag 25 november 2005 @ 16:09:
Een tabel maken met alle dagen van het jaar erin, daaruit selecteren en dan een left join met deze tabel is denk ik de makkelijkste oplossing. Zoiets:


[...]


Eventueel moet je nog een ISNULL gebruiken om een 0 terug te geven als een record niet bestaat in de bezoekers tabel maar ik weet even niet het MySQL equivalent van deze SQL Server functie.

Is dat niet een beetje overkill? En wil je dan alle mogelijke data daarin zetten?

[P_de_B]

Verwijderd schreef op vrijdag 25 november 2005 @ 16:11:
[...]

Is dat niet een beetje overkill? En wil je dan alle mogelijke data daarin zetten?

Of alle nummers van 1 t/m 366 en dan joinen met een datediff oid, het gaat om het idee. Het gaat erom een goede set-based oplossing te vinden, ik heb jou nog niet een betere suggestie zien doen?

[whoami]

Nee, da's geen overkill imo want je moet die data toch ergens vandaan krijgen ?

Stel, je hebt dit in je tabel:
2005-11-12
2005-11-12
2005-11-14
2005-11-15

dan wil je deze output:
12/11/2005 : 2 bezoekers
13/11/2005 : 0 bezoekers
14/11/2005 : 1 bezoeker
15/11/2005 : 1 bezoeker

Echter, als 2005-11-13 zich nergens in je DB bevind, dan kan je 'm ook moeilijk tevoorschijn toveren.

[Verwijderd]

_{Hmms, ja jullie hebben gelijk... Laten we maar weekend gaan vieren...}

[NMe]

Ik denk dat het eenvoudiger is om dat in je applicatie zelf af te handelen, in plaats van in MySQL. Scheelt je al dat gekloot met schrikkeljaren en dergelijke.

[P_de_B]

-NMe- schreef op vrijdag 25 november 2005 @ 16:21:
Ik denk dat het eenvoudiger is om dat in je applicatie zelf af te handelen, in plaats van in MySQL. Scheelt je al dat gekloot met schrikkeljaren en dergelijke.

Dan zou ik dus voor zo'n nunmertabel gaan en dan een dateadd(d,'1 januari', nummertje) doen, gecombineerd met een where om de laatste dag 31 december te laten zijn.

[Verwijderd]

Kan bijv zo:

PHP:

$res = mysql_query("SELECT stamp, COUNT(stamp) as aantal FROM bezoeken  GROUP BY stamp");
$obj = mysql_fetch_assoc($res);
$previous = $obj['stamp']); // 1e stamp
echo(date($previous, ...) . $obj['aantal']);
while ($obj = mysql_fetch_assoc($res)) {
  $current = $obj['stamp'];
  $previous += 3600;
  while (date($previous, ..) != date($current, ...)) {
    // 0 bezoekers
    echo(date($previous, ...). " 0");
    $previous += 3600;
  }
  echo(date($current, ...). $obj['aantal'];
  
}

Naamgeving is misschien niet zo netjes, maar volgens mij werkt het zo wel

[ Voor 8% gewijzigd door Verwijderd op 25-11-2005 17:59 ]

[Tijsje]

Ik heb toch maar gewoon een tabel met alle datums aangemaakt.
Voor de mensen die het ook nog ooit zo gaan oplossen, hier alvast de code om de tabel te vullen:

PHP:

    connect_db();
    $jaar   = 2005;
    $maand  = 1;
    $dag    = 1;
        
    while ($dag < 10000)
    {
        $datum = date("Y-m-d",mktime (0,0,0,$maand ,$dag,$jaar));
        $insert = "INSERT INTO `datums` (`datum`) VALUES ('$datum');";
        mysql_query($insert);   
        $dag++; 
    }

Bedankt voor de hulp!