[MySQL] De sum van een select met COUNT

[FireFoz]

Ik ben al de hele ochtend aan het stoeien en kom er maar niet uit. Ik probeer uit een grote tabel (meer dan 100,000 records) het aantal ip's te selecteren dat minder dan x keer voor komt; laat zeggen in dit geval 3.

De tabel is klein en bestaat uit:

- ip (varchar, 15)
- site (tinyint, 1)
- datetime

Nou heb ik het al wel voor elkaar gekregen om de ip-addressen die minder dan 3 keer voorkomen te selecteren:

code:

SELECT COUNT( ip ) AS `totalfound` 
FROM `tracking` 
WHERE `site` = '1'
GROUP BY `ip` 
HAVING `totalfound` < '3'

Met deze query krijg ik dus per ip een rij terug met het aantal keer dat ie voorkomt. Dat is dus al een stap in de goede richting, maar nu wil ik nog het totaal van al deze rijen hebben. Als ik dit door middel van een while lus doe met fetch_array, dan kost dit best veel tijd. Volgensmij kan dit veel sneller my mysql dmv SUM, maar ik krijg dat niet toegepast op deze query. Wat ik heb geprobeerd is:

code:

SELECT COUNT( ip ) AS `totalfound`, SUM('totalfound')
FROM `tracking` 
WHERE `site` = '1'
GROUP BY `ip` 
HAVING `totalfound` < '3'

code:

SELECT SUM FROM (SELECT COUNT( ip ) AS `totalfound`, SUM('totalfound')
FROM `tracking` 
WHERE `site` = '1'
GROUP BY `ip` 
HAVING `totalfound` < '3')

code:

SELECT COUNT( ip ), SUM(COUNT( ip )) AS `totalfound`
FROM `tracking` 
WHERE `site` = '1'
GROUP BY `ip` 
HAVING COUNT( ip ) < '3'

en nog wat meer varianten hier op die ik niet meer weet. Het ist och wel mogelijk Weet iemand hoe ik dit voor elkaar kan krijgen met MySQL (op een snelle manier)? Of misschien zelfs zonder count op een andere manier die nog sneller is:) Snelheid is in elk geval het belangrijkst want deze query moet meerdere malen uitgevoert gaan worden. Ik heb MySQL 5.0.45.

[ATS]

Errr...
Zoiets?

code:

SELECT Count(tracking.IP) AS CountVanIP, tracking.IP
FROM tracking
GROUP BY tracking.IP
HAVING ((Count(tracking.IP) < 3));

Edit: laat maar, dit zocht je dus niet...

Edit 2:
Je zocht meer dit:

code:

SELECT Count(IP) AS Cnt FROM 
  (SELECT Count(tracking.IP) AS CountVanIP, tracking.IP
  FROM tracking
  WHERE site=1
  GROUP BY tracking.IP
  HAVING (((Count(tracking.IP))<3))
);

Dit levert het aantal unieke IP's op dat minder dan drie keer voorkomt en waar site = 1. Of zocht je eigenlijk het totaal aantal entries in je tabel waar dat weer op gebaseerd is? Dat is een triviale toevoeging:

code:

SELECT Count(IP) AS cnt, SUM(CountVanIP) AS sum FROM 
  (SELECT Count(tracking.IP) AS CountVanIP, tracking.IP
  FROM tracking
  WHERE site=1
  GROUP BY tracking.IP
  HAVING (((Count(tracking.IP))<3))
);

[ Voor 81% gewijzigd door ATS op 28-08-2007 13:16 ]

[FireFoz]

Ik denk dat die 2e code in buurt komt van wat ik zoek, alleen die geeft een error:

#1248 - Every derived table must have its own alias

Deze kreeg ik ook steeds toen ik probeerde meerdere queries te gebruiken..

-edit- bij nader inzien toch de 3e Ik wil dus het totaal aantal hits hebben tot een maximum van 2 () per ip. Dus als ik 5 records heb voor ip A, moet ie er maximaal 3 van gebruiken.
Stel ik heb:

ip A 3 hits
ip B 5
ip C 1
ip D 20

dan zou er uit dus 2+2+1+2=7 uit moeten komen. Ik denk dat de 3e query dit wel doet, alleen deze geeft dezelfde foutmelding..

-edit-

code:

SELECT Count(IP) AS cnt, SUM(CountVanIP) AS sum FROM 
  (SELECT Count(tracking.IP) AS CountVanIP, tracking.IP
  FROM tracking
  WHERE site=1
  GROUP BY tracking.IP
  HAVING (((Count(tracking.IP))<3))
)  AS naam;

zo is de foutmelding weg maar nog geen resultaten...ff verder stoeien

[ Voor 68% gewijzigd door FireFoz op 28-08-2007 13:44 ]

[ATS]

Ik wil dus het totaal aantal hits hebben tot een maximum van 2 () per ip. Dus als ik 5 records heb voor ip A, moet ie er maximaal 3 van gebruiken.

Oh... dat is wéér iets anders! Dat is niet wat je eerst vroeg, en ook niet wat je gaat krijgen met deze query. Deze telt namelijk niet de IP's mee die meer dan 3 keer voor komen. Overigens zijn deze queries gemaakt met Access (heb ik hier nu eenmaal bij de hand, en MySQL niet), dus je zal wellicht inderdaad even wat moeten spelen met de precieze code. Wat die foutmelding betekent kan je vast opzoeken in de MySQL documentatie...

Maargoed, dit helpt je natuurlijk nog altijd niet, omdat je iets anders nodig hebt dan je eerst vroeg (voor zover ik je vraag begreep). Ik weet niet zo goed of wat je wil kan in MySQL. Je kan vast wel functies gebruiken, en die heb je nodig. Eigenlijk zou je dit willen doen:

code:

SELECT Count(IP) AS cnt, SUM(CountVanIP) AS sum FROM 
  (SELECT min(2, Count(tracking.IP)) AS CountVanIP, tracking.IP
  FROM tracking
  WHERE site=1
  GROUP BY tracking.IP
);

Let op de funcitie "min(a, b)" die in de binnenste clause staat. Deze levert de laagste waarde van de twee argumenten. Dat (of zoiets) kan in Access, maar ik weet niet of dat in MySQL ook kan. Je moet dan natuurlijk ook de HAVING weg halen.

[ Voor 11% gewijzigd door ATS op 28-08-2007 13:48 ]

[FireFoz]

Je hebt gelijk, ik zat raar te blaten:) was ff in de war. De 3e query heb ik nu aan de praat gekregen en hij lijkt te werken; het is wel wat ik zoek! thanks!