[SQL] GROUP BY werkt niet, of interpreteer ik verkeerd?

[Knakker]

Ik heb een tabel 'Views' in mijn database die bijhoudt hoevaak een bepaald item wordt bekeken, en wel in deze vorm:

ID - ItemID - Datum

Ik houd het bij op deze manier (ipv een extra item Views bij de rest van de Item-eigenschappen) omdat ook per (willekeurige) tijdreeks gezien moet kunnen worden hoevaak een bepaald item bekeken is. Door middel van een simpele query

SELECT COUNT(ID) AS AantalViews FROM Views WHERE ItemID = [nummer] AND Month(Datum) = [nummer vd maand] AND Year(Datum) = [jaartal]

met een eventuele extra WHERE-clause erbij kan ik eenvoudig per afzonderlijk item bekijken hoeveel views er per maand zijn geweest. Dit werkt allemaal prima.


Nu wil ik een overzichtspagina genereren; alle items onder elkaar en dan 12 kolommen met het aantal views van elk item in elk van de afgelopen 12 maanden. Ik dacht dit eenvoudig te kunnen doen door gewoon een GROUP BY te gebruiken ...

SELECT COUNT(ID) AS AantalViews FROM Views WHERE Month(Datum) = [nummer vd maand] AND Year(Datum) = [jaartal] GROUP BY ItemID

... zodat ik dus een array zou krijgen, aantal records gelijk aan het aantal unieke items (meer dan 250), waaruit ik zo het aantal pageviews kan aflezen. Helaas, niks is minder waar. In plaats van een array krijg ik gewoon 1 waarde van het totaal aantal views van alle items uit die maand bij elkaar.

Interpreteer ik het GROUP BY verkeerd, of ... ?

Bij voorbaat dank!

[whoami]

Je moet hier niet groeperen op ID, want dan krijg je idd iedere keer maar 1 terug als resultaat van de count.
-> Waarom: group by gaat er dus voor zorgen dat er gegroepeert wordt op id. (Nou, duh)
Aangezien 'id' uniek is, en dus maar 1x kan voorkomen kan het resultaat van die count ook maar iedere keer 1 zijn.

Als je nu eens je query als volgt maakt:

code:

select count(*), month(datum) as maand
from tabel
....
group by maand

Dan krijg je het aantal records te zien per maand (gegroepeerd per maand dus).

* whoami legt het waarschijnlijk een beetje wazig uit....

Hoe ziet jouw tabelstructuur er eigenlijk uit?

[chris]

hmz weetje wat het is: count hangt van group by af. beetje moeilijk om uit te leggen en dus. volgensmij staat er wel wat in de man.

[Dash2in1]

Als je per ItemId meerdere Id's hebt interpreteer je het goed, daar lijkt het echter niet op, aangezien je steeds 1 terug krijgt.

[cameodski]

Ik denk dat je een crosstab query nodig hebt.

Knakker schreef op 30 september 2002 @ 20:57:
SELECT COUNT(ID) AS AantalViews FROM Views WHERE ItemID = [nummer] AND Month(Datum) = [nummer vd maand] AND Year(Datum) = [jaartal]

Als je op deze manier filtert op datum, loop je een groot risico, dat een eventuele index op datum niet gebruikt kan worden. Je kunt beter iets met BETWEEN doen en dan gewoon de begin- en einddatum van de betreffende maand erbij zetten.
Dit lijkt een tabel te zijn die erg groot kan worden, dus misschien wel belangrijk om even rekening mee te houden.

[robjanssen]

Werkt dit niet:

code:

SELECT Month(Datum), COUNT(ID) AS AantalViews
FROM Views
WHERE ItemID = [nummer]
AND Month(Datum) = [nummer vd maand]
AND Year(Datum) = [jaartal]
GROUP BY Month(Datum)

[Verwijderd]

robjanssen schreef op 30 september 2002 @ 23:30:
Werkt dit niet:

code:

GROUP BY Month(Datum)

Dat kan volgens mij niet...

[Just_a_Gamer]

Ja dat werkt.

Ow k de month functie bestaat wel en haalt de maand uit de datum gewoon. Deze functie wordt door meeste database ondersteund onder andere door Oracle, Access en Mysql volgens mij.

edit2: Alleen niet bij group by

edit:
zo goed whoami?

[whoami]

Just_a_Gamer schreef op 01 oktober 2002 @ 08:54:
Ja dat werkt.

Ahum, zinloze replies worden niet geapprecieerd.

[Verwijderd]

Wil je de code hiervan:
h**p://www.curvycuties.com/count.php?perp=hoei
om de queries even te bestuderen...?

[ Voor 0% gewijzigd door Verwijderd op 01-10-2002 20:42 . Reden: iemand zei 'sesamstraat'... ranking mijn kont?! ]

[D2k]

onderhoud
ff dicht

[whoami]

Just_a_Gamer schreef op 01 oktober 2002 @ 08:54:

edit2: Alleen niet bij group by

Toch nog even een reply,

Als je het zo doet, moet het wel werken:

code:

select count(*), month(datum) as maand
from tabel
group by maand

[D2k]

heropend

[Knakker]

Ik heb mezelf niet duidelijk uitgelegd denk ik

Ik heb dus een tabel genaamd Views met 3 kolommen: ID (uniek voor iedere view), ItemID (het ID nummer van het item waar de view bij hoort) en Datum (spreekt voor zich). Een voorbeeld zou dus kunnen zijn

code:

1 / 1 / 29-09-2002
2 / 1 / 30-09-2002
3 / 2 / 30-09-2002
4 / 1 / 30-09-2002
5 / 1 / 01-10-2002

Met andere woorden: Item 1 (wat dat ook maar mogen zijn) heeft 4 views gehad: 1 op 29 september, 2 op 30 september en 1 op 1 oktober. Item 2 heeft er slechts 1 gehad, namelijk op 30 oktober.


Nu wil ik voor elk item het aantal views hebben in de september. Omdat er maar 2 verschillende items zijn kan ik heel simpel 2 queries uitvoeren (in ASP, maar het gaat hier puur om de SQL):

code:

For I = 1 to 2
  SQL = "SELECT COUNT(ID) As AantalViews FROM Views " & _
      "WHERE ItemID = " & I & " AND " _
      "Month(Datum) = 9 AND Year(Datum) = 2002"
  QueryResultaat = DB.Execute(SQL)
  Response.Write(QueryResultaat("AantalViews"))
Next

Voor 2 items maakt dat geen hol uit, maar als ik het aantal views van 250 items op moet vragen in 12 verschillende maanden dan ben je ineffecient bezig als je 3000 queries uitvoert

Wat ik dus zoek is een SQL query waarbij ik een array terug krijg met 250 records; hiervan is de 1e het aantal views in september van item 1, de 2e het aantal views in september van item 2 - etcetera. Respectievelijk 3 en 1 in dit geval.

Waarom COUNT(ID) en GROUP BY ItemID volgens mij? Omdat ik het aantal unieke IDs wil hebben (= dus het aantal views) gegroepeerd per ItemID. Maar ik doe dus iets fout, want ik krijg maar 1 waarde terug in plaats van een array

De bovenstaande oplossing werkt niet in ieder geval: U probeert een query uit te voeren waarbij de opgegeven expressie MONTH(Datum) geen deel uitmaakt van een statistische functie.


Bedankt voor de hulp tot nu toe, en alvast bedankt voor de toekomstige hulp

[cameodski]

Is het veld ID uniek in de tabel VIEWS? Zo ja, dan kun je COUNT (ID) beter vervangen door COUNT (*).

code:

SELECT  ItemID,
    Year(Datum) AS Jaar,
    Month(Datum) AS Maand,
    COUNT (ID) AS Aantal
FROM    Views
WHERE   blabla
GROUP BY ItemID,
    Year(Datum),
    Month(Datum)

Misschien dat je in je GROUP BY ipv Year(Datum) en Month(Datum), Jaar en Maand moet gebruiken. (Hangt af van RDBMS.)

Je krijgt nu aantallen terug, gegroepeerd op item, jaar en maand.
Als je echter ItemID, AantalMaand1, AantalMaand2, ..., AantalMaand12 terug wilt hebben, zul je de eerder genoemde crosstab query moeten gebruiken. Dan wordt je query alleen wel een stuk(je) ingewikkelder.

[Joeroenho]

Euh... is het niet ORDER BY..?

[Dash2in1]

code:

select itemid, count(id), to_date(datum,"Mon") as maand
from je_tabel
group by ItemId, maand
order by maand, ItemId

Of welke ordering je wilt. Het kan ook zijn dat je iets anders nodig hebt voor to_date.
Succes.

edit:

Of wat cemeodski voorstelt

[cameodski]

El_GringO schreef op 01 oktober 2002 @ 17:39:
Euh... is het niet ORDER BY..?

Voorzover ik de TS goed begrijp, is hij op zoek naar totalen per item, per jaar, per maand. Een order by groepeert niks, dus daar schiet je niks mee op.

[Joeroenho]

maar volgens mij wil hij ook sorteren, dus zal ie ORDER BY toch moeten gebruiken...

[cameodski]

El_GringO schreef op 01 oktober 2002 @ 17:43:
maar volgens mij wil hij ook sorteren, dus zal ie ORDER BY toch moeten gebruiken...

Oh, dat zou best kunnen.
Als de group by werkt, kunnen we nog wel eens over de sortering gaan nadenken.

[Verwijderd]

de oplossing van cameodski lijkt me de juiste...

werkt het?

[Knakker]

Nee het werkt niet.... weer krijg ik 1 waarde terug in plaats van een array met 250 waarden

[cameodski]

Als je maar 1 waarde terug krijgt, denk ik dat er toch echt iets anders aan de hand is. Hoe lees je de resultset uit? En wat voor RDBMS gebruik je trouwens?

[whoami]

Knakker schreef op 01 oktober 2002 @ 21:23:
Nee het werkt niet.... weer krijg ik 1 waarde terug in plaats van een array met 250 waarden

Voer de query gewoon eens uit mbhv een tool zoals Query Analyzer oid. Dus niet direct in jouw programma/script.

[Verwijderd]

Knakker schreef op 01 oktober 2002 @ 21:23:
Nee het werkt niet.... weer krijg ik 1 waarde terug in plaats van een array met 250 waarden

Ik neem aan dat je dan geen where clause hebt gebruikt bij de oplossing van Cameodski......

zou zelf nl dezelfde neergezet hebben

[Knakker]

Omdat alles volgens mij echt moet kloppen, post ik hier maar de hele betreffende code.

Het draait om scholen; de website waar ik voor bezig ben heeft een hele hoop MBOs, HBOs en Universiteiten in haar database staan. De opdrachtgever wil kunnen zien (in 1 pagina) hoevaak elke school bekeken is per maand.

code:

<html><head><link href='style.css' rel='stylesheet' type='text/css'></head>
<body bgcolor="#e6e6e6"><font class="title">Statistieken</font><br><br>
<table width="790" border="0" cellspacing="0" cellpadding="0">
<tr><td width='310'><b>School</b></td><%
  
Maand = DatePart("M", Date)
Jaar = DatePart("YYYY", Date)
VorigJaar = Jaar - 1
  
For I = Maand To 1 STEP -1
  Response.Write("<td width='40' align='right'><b>" & I & "/" & Right(Jaar,2) & "</b></td>")
Next

If Maand < 12 Then
  For I = 12 To Maand + 1 STEP -1  
    Response.Write("<td width='40' align='right'><b>" & I & "/" & Right(VorigJaar,2) & "</b></td>")
  Next
End If  
    
Set Con = Server.CreateObject("ADODB.Connection")
Con.ConnectionString = "DSN=BKK"
Con.Open  
  
SQL = "SELECT Id, Schoolnaam FROM Scholen ORDER BY Schoolnaam"
Set QR = Con.Execute(SQL)
Response.Write("<tr><td valign='top'><table width='310'border='0' cellspacing='0' cellpadding='0'>")
While NOT QR.EOF
  Response.Write("<tr><td>" & QR("Schoolnaam") & "</td></tr>")
  QR.MoveNext
Wend
Response.Write("</table></td>")
    
For I = Maand To 1 STEP -1
  SQL = "SELECT SchoolID, Month(Datum) AS Maand, Year(Datum) AS Jaar" & _
         ", COUNT (*) AS Aantal FROM Views WHERE Month(Datum) = " & I & " AND " & _
         "Year(Datum) = " & Jaar & " GROUP BY SchoolID, Year(Datum), Month(Datum)"

  Set QRT = Con.Execute(SQL)
  Response.Write("<td valign='top'><table width='40' border='0' cellspacing='0' cellpadding='0'>")
  While NOT QRT.EOF
    Response.Write("<tr><td>" & QRT("Aantal") & "</td></tr>")
    QRT.MoveNext
  Wend
  Response.Write("</table></td>")
Next

If Maand < 12 Then
  For I = 12 To Maand + 1 STEP -1
    SQL = "SELECT SchoolID, Month(Datum) AS Maand, Year(Datum) AS Jaar" & _
           ", COUNT (*) AS Aantal FROM Views WHERE Month(Datum) = " & I & " AND " & _
           "Year(Datum) = " & Jaar & " GROUP BY SchoolID, Year(Datum), Month(Datum)"
    Set QRT = Con.Execute(SQL)
    Response.Write("<td valign='top'><table width='40' border='0' cellspacing='0' cellpadding='0'>")
    While NOT QRT.EOF
      Response.Write("<tr><td>" & QRT("Aantal") & "</td></tr>")
      QRT.MoveNext
    Wend
    Response.Write("</table></td>")
  Next
End If
  
QR.Close
QRT.Close
Con.Close
%></tr></table></body></html>

Ik heb dus een tabel met 13 kolommen; in ieder van de 13 kolommen wordt een nieuwe tabel gemaakt met het aantal rijen gelijk aan het aantal scholen.

edit:

Ik heb even een voorbeeld output online gezet: http://members.home.nl/tbouwstraat/voorbeeld.html

[Knakker]

Ik ben erg druk geweest laatste 2-3 dagen, en ik zie dat nog niemand gereageerd heeft. Heeft iemand een idee?