[ACCESS/SQL]Count Distinct probleem

[BlackBurn]

Voor mijn site ben ik een simpel statistieken paginaatje in ASP aan het maken van het aantal bezoekers.

Nu heb ik een tabel gemaakt met daarin o.a. de datum en het ip (ga nog een andere referrer gebruiken).
Nu wil ik de "unieke" bezoekers per dag tellen, normaal gesproken zou dit volgens mij zo moeten werken.

code:

SELECT tblStats.Datum, COUNT(DISTINCT ip)
FROM tblStats
GROUP BY tblStats.Datum;

Nu kwam ik er na enig zoeken achter (op dit forum zelfs) dat Access dit niet ondersteund en dat je een subquery moest gebruiken, maar als ik iets probeer in de trand van COUNT(query) dan gaat Access over zijn nek, ik krijg twee verschillende foutmeldingen, of een foutmelding dat er geen foutmelding voor het probleem is of dat de query meer dan 2 rijen kan teruggeven (wat niet klopt overigens).

Iemand enig idee voor een oplossing, workaround of waar ik meer informatie hier over kan vinden. Aangezien het maar een klein databeestje is, wil ik hem gewoon in Acces houden, lekker makkelijk en lui

[P_de_B]

SELECT Datum, IP, COUNT(*) AS Aantal
FROM tbl
GROUP BY Datum, IP

Dit geeft je per dag, per IP het aantal hits.

[BlackBurn]

P_de_B schreef op dinsdag 15 maart 2005 @ 16:47:
SELECT Datum, IP, COUNT(*) AS Aantal
FROM tbl
GROUP BY Datum, IP

Dit geeft je per dag, per IP het aantal hits.

Ben bang dat het toch iets lastiger is, dit geeft een optelling per ip-adres bijv.
127.0.0.1 staat er twee keer in op 3/15/2005 dan geeft de count 2 terug.

Het is de bedoeling dat als er 7 verschillende ip-adressen op 15-3-2005 zijn geweest, dat die dan de 7 als resultaat geeft, vandaar de distinct.

[Verwijderd]

Ik weet niet of Access dit pikt, maarre... zoiets?

code:

SELECT 
  DISTINCT a.Datum, 
  (SELECT COUNT(DISTINCT ip) from tblStats where Datum = a.Datum) as Aantal
FROM tblStats a
GROUP BY a.Datum

[ Voor 5% gewijzigd door Verwijderd op 15-03-2005 17:47 ]

[BlackBurn]

Het probleem is, dat Access dit gedeelte niet pikt.

code:

COUNT(DISTINCT ip)

[mylar]

code:

select datum, count(*) as hits
from tblStats
group by datum

Dit zou je een overzicht moeten geven van alle hits per datum.

Het zou ook altijd kunnen dat access moeilijk gaat doen over de tijd, die wordt namelijk samen met de datum opgeslagen in het zelfde veld. Maar zolang die dus voor heel de dag hetzelfde is, is dit geen probleem (bv 0:00:00).

[ Voor 45% gewijzigd door mylar op 15-03-2005 18:04 ]

[BlackBurn]

mylar schreef op dinsdag 15 maart 2005 @ 18:01:

code:

select datum, count(*) as hits
from tblStats
group by datum

Dit zou je een overzicht moeten geven van alle hits per datum.

Het zou ook altijd kunnen dat access moeilijk gaat doen over de tijd, die wordt namelijk samen met de datum opgeslagen in het zelfde veld. Maar zolang die dus voor heel de dag hetzelfde is, is dit geen probleem (bv 0:00:00).

Aangezien de data met een ASP script word opgeslagen heb ik ervoor gezorgd dat alleen de datum op word geslagen, dit gaat dus perfect, het scriptje wat je geeft draait ook perfect, maar het gaat er juist om, dat hij de verschillende ip-adressen telt en niet dezelfde, van de 10 bezoekers kunnen er goed maar 2 "uniek" zijn.

Dus zitten we weer met die Distinct, mocht het nu echt niet mogelijk zijn ga ik er wel een ASP scriptje voro schrijven, maar dat geeft natuurlijk wel meer load en is wat slordiger.

[jvdmeer]

BlackBurn schreef op dinsdag 15 maart 2005 @ 16:51:
[...]

Ben bang dat het toch iets lastiger is, dit geeft een optelling per ip-adres bijv.
127.0.0.1 staat er twee keer in op 3/15/2005 dan geeft de count 2 terug.

Het is de bedoeling dat als er 7 verschillende ip-adressen op 15-3-2005 zijn geweest, dat die dan de 7 als resultaat geeft, vandaar de distinct.

Je kan na het uitvoeren van deze query toch opvragen hoeveel "affected rows" er zijn? Het is een beetje een omweg, maar daar moet je maar aan wennen bij Access.

De werkende omweg:

SQL:

SELECT datum, Count(ip) AS AantalVanIp
FROM (SELECT Tabel1.datum, Tabel1.ip, count(*)
FROM Tabel1
GROUP BY Tabel1.datum, Tabel1.ip)
GROUP BY datum;

[ Voor 16% gewijzigd door jvdmeer op 15-03-2005 19:00 ]

[mylar]

Zo dan:

code:

select datum, count(*) as hits
from 
    (select datum
     from tblStats
     group by ip, datum)
group by datum

[BlackBurn]

jvdmeer schreef op dinsdag 15 maart 2005 @ 18:46:
[...]


Je kan na het uitvoeren van deze query toch opvragen hoeveel "affected rows" er zijn? Het is een beetje een omweg, maar daar moet je maar aan wennen bij Access.

De werkende omweg:

SQL:

SELECT datum, Count(ip) AS AantalVanIp
FROM (SELECT Tabel1.datum, Tabel1.ip, count(*)
FROM Tabel1
GROUP BY Tabel1.datum, Tabel1.ip)
GROUP BY datum;

Dit werkt perfect, moest de code wel 3x doorlezen voordat ik goed snapte wat er gebeurde.

mylar schreef op dinsdag 15 maart 2005 @ 20:18:
Zo dan:

code:

select datum, count(*) as hits
from 
    (select datum
     from tblStats
     group by ip, datum)
group by datum

Dit werkt eveneens perfect, dacht eigenlijk dat ik het zo al geprobeerd, had schijnbaar toch foutje gemaakt in Gorup By ofzo.

In ieder geval iedereen harstikke bedankt, kan ik weer lekker verder rommelen

[sopsop]

BlackBurn schreef op woensdag 16 maart 2005 @ 00:15:
Dit werkt eveneens perfect, dacht eigenlijk dat ik het zo al geprobeerd, had schijnbaar toch foutje gemaakt in Gorup By ofzo.

dat denk ik ook

[ Voor 7% gewijzigd door sopsop op 16-03-2005 10:02 ]

[BlackBurn]

boppert schreef op woensdag 16 maart 2005 @ 10:01:
[...]

dat denk ik ook

Haha, lol, het begon al laat te worden he Ben al lang blij dat het nu werkt.

[BlackBurn]

Hmm toch nog klein probleempje waar ik niet uitkom, naast het veld "unieke bezoekers", wil ik ook het totaal aantal bezoekers, nu dacht, dat dit niet zo lastig was als ik een alias zou aanmaken voor de tabel. dus zoiets.

code:

SELECT T.datum, Count(T.IP) AS Uniek, Count(S.IP) AS Totaal
FROM (SELECT datum, ip
     FROM tblStats
     GROUP BY ip, datum) AS T, tblStats AS S
GROUP BY T.datum, S.datum;

Waar kijk ik hier overheen, het gaat eigenlijk mis vanaf dat ik tblStats AS S toevoeg, dan krijg ik al rare waardes in mijn count.

Iemand een hint waar ik op moet letten, naar moet kijken?

[Verwijderd]

BlackBurn schreef op woensdag 16 maart 2005 @ 11:11:
Hmm toch nog klein probleempje waar ik niet uitkom, naast het veld "unieke bezoekers", wil ik ook het totaal aantal bezoekers, nu dacht, dat dit niet zo lastig was als ik een alias zou aanmaken voor de tabel. dus zoiets.

code:

SELECT T.datum, Count(T.IP) AS Uniek, Count(S.IP) AS Totaal
FROM (SELECT datum, ip
     FROM tblStats
     GROUP BY ip, datum) AS T, tblStats AS S
GROUP BY T.datum, S.datum;

Waar kijk ik hier overheen, het gaat eigenlijk mis vanaf dat ik tblStats AS S toevoeg, dan krijg ik al rare waardes in mijn count.

Iemand een hint waar ik op moet letten, naar moet kijken?

Waar is je join ?

[BlackBurn]

Verwijderd schreef op woensdag 16 maart 2005 @ 11:51:
[...]


Waar is je join ?

Hmm, heb het nu zo,

code:

SELECT T.datum, S.datum, Count(T.IP) AS Uniek, Count(S.IP) AS Totaal
FROM (SELECT tmp.datum, tmp.ip
     FROM tblStats AS tmp
     GROUP BY tmp.ip, tmp.datum) AS T, tblStats AS S
WHERE t.datum= s.datum
GROUP BY T.datum, S.datum

Nu zie ik wel alle datums netjes en de velden, alleen de waardes kloppen van geen kanten en kan ook niet achterhalen waar die ze vandaan haalt, wil evt. de db wel posten, beetje moeilijk uit te leggen zo.

edit: Heb wel door waar de waardes vandaan komen, maar hoe ze onstaan nog niet, hij pakt het kwadraat van het aantal, dus als het aantal bezoekers 20 is, dan word het 400 en als het 7 is 49 etc.
Er zit ook geen verschil meer tussen de unieke en het totaal aantal bezoekers

[ Voor 19% gewijzigd door BlackBurn op 16-03-2005 12:05 ]

[bigbeng]

Je moet wel joinen op een unieke relatie, anders krijg je een zogeheten inprodukt. Als een specifieke datum 10x voorkomt, dan komt bij jouw join 10x10 records terug die vervolgens ook gecount worden. Je moet dus de unieke sleutel gebruiken van tblstats om op te joinen. Ik hoop dat je wel een primary key op tblStats hebt?

edit:
Ik denk dat het helemaal niet kan op deze manier. Count telt volgens mij gewoon het aantal records en zoals je al aangaf ondersteunt Access Count(Distinct) niet.
Wat je volgens mij kan doen is queries in access maken:
query1=select count bezoekers
query2=select count unieke bezoekers
query3=select count bezoekers, count unieke bezoekers from query1, query2

[ Voor 37% gewijzigd door bigbeng op 16-03-2005 12:59 ]

[BlackBurn]

bigbeng schreef op woensdag 16 maart 2005 @ 12:52:
Je moet wel joinen op een unieke relatie, anders krijg je een zogeheten inprodukt. Als een specifieke datum 10x voorkomt, dan komt bij jouw join 10x10 records terug die vervolgens ook gecount worden. Je moet dus de unieke sleutel gebruiken van tblstats om op te joinen. Ik hoop dat je wel een primary key op tblStats hebt?

edit:
Ik denk dat het helemaal niet kan op deze manier. Count telt volgens mij gewoon het aantal records en zoals je al aangaf ondersteunt Access Count(Distinct) niet.
Wat je volgens mij kan doen is queries in access maken:
query1=select count bezoekers
query2=select count unieke bezoekers
query3=select count bezoekers, count unieke bezoekers from query1, query2

Heb wel een primary key op een veld met autonummering, maar zie zo, 1,2,3 niet hoe ik daar met de join gebruik van kan maken.

Het probleem is eigenlijk dat het uiteindelijk in een ASP script komt en dan word uitgepoept d.m.v. een loopje. Het word dan lastig als ik met meerdere queries moet werken.

Pff, dat zo'n simpel ideetje nog zoveel moeite kan kosten

edit:

query1=select count bezoekers
query2=select count unieke bezoekers
query3=select count bezoekers, count unieke bezoekers from query1, query2

Dit zou dan eigenlijk toch ook in een Query moeten kunnen?
Want volgens mij is het hetzelfde als we nou proberen alleen dan wat opgesplitst.

[ Voor 13% gewijzigd door BlackBurn op 16-03-2005 13:07 ]

[bigbeng]

Klopt, je zou het ook met

code:

SELECT ...
FROM (SELECT <dit is je count distinct>), (SELECT <dit is je count algemeen>)
WHERE etc...

kunnen doen. Het kan in elk geval niet met een join zoals je nu probeert.

Maar persoonlijk vind ik het wat overzichtelijker om losse queries te maken voor losse functionaliteiten. Dat houdt je query wat meer to the point.

[BlackBurn]

Thanks bigbeng, dat was even de hint die ik nodig had

Losse queries is inderdaad overzichterlijker, maar ik wilde het als 1 geheel in de asp code proppen vandaar.

de oplossing:

code:

SELECT tblUniek.Datum, Uniek, Totaal 
FROM (SELECT datum, COUNT(*) AS Uniek
            FROM (SELECT datum
                       FROM tblStats
                       GROUP BY  ip, datum)
            GROUP BY  datum) AS tblUniek,
           (SELECT datum, COUNT(*) AS Totaal
            FROM tblStats
            GROUP BY datum) AS tblTotaal
WHERE tblUniek.datum= tblTotaal.datum

Alleen had ik verwacht, dat er nog een group by nodig zou zijn, maar dat was dus niet het geval.

Iedereen bedankt voor de hulp.