Hente de sidste 7 dages besøgstal

Det må være noget i stil med

SELECT *,COUNT(id) AS antal FROM siterater_statistik GROUP BY "her skal der tages hensyn til hvilken dag" ORDER BY antal DESC LIMIT 7

Ikke testet - prøv selv:
SELECT id, EXTRACT(YEAR_MONTH_DAY, datofelt) AS dato, COUNT(id) AS antal FROM siterater_statistik GROUP BY ID, dato ORDER BY antal DESC LIMIT 7

dato delen kan også udtrækkes med:
DATE_FORMAT(datofelt, "%Y%m%d") as dato

Skal der ikke være en WHERE tidspunkt > (datoen for 7 dage siden)?

Hovsa!

SELECT EXTRACT(YEAR_MONTH_DAY, datofelt) AS dato, COUNT(*) AS antal FROM siterater_statistik GROUP BY dato ORDER BY dato DESC LIMIT 7

LIMIT 7 viser så kun de sidste 7 dage.

Sidste syv dage incl. idag - sorteret efter antal:
SELECT EXTRACT(YEAR_MONTH_DAY, datofelt) AS dato, COUNT(*) AS antal FROM siterater_statistik
  WHERE TO_DAYS(NOW()) - TO_DAYS(datofelt) < 7
GROUP BY dato
ORDER BY antal DESC

SELECT EXTRACT(YEAR_MONTH_DAY, tidspunkt) AS dato, COUNT(*) AS antal FROM siterater_statistik WHERE TO_DAYS(NOW()) - TO_DAYS(tidspunkt) < 7 GROUP BY dato ORDER BY antal DESC

Virker ikke, det gør de tidligere eksempler heller ikke.

Den sidste giver fejlen

You have an error in your SQL syntax near 'YEAR_MONTH_DAY, tidspunkt) AS dato, COUNT(*) AS antal FROM siterater_statistik W' at line 1

Jeg fik det til at virke med det her:


SELECT UNIX_TIMESTAMP(tidspunkt) AS tidspunkt, EXTRACT(DAY FROM tidspunkt) as tid2, COUNT(id) AS antal FROM siterater_statistik WHERE TO_DAYS(NOW()) - TO_DAYS(tidspunkt) < 8 GROUP BY tid2 ORDER BY tidspunkt DESC LIMIT 7

Tak for skubbet i den rigtige retning :)

SELECT id, count(tidspunkt)
FROM siterater_statistik
WHERE TO_DAYS(NOW()) - TO_DAYS(tidspunkt) <= 7
GROUP BY id