AVG og Count

AVG() og tilsvarende skal du bruge sammen med en group by.

fx

select varenavn, count(*), avg(salgspris), max(salgspris), min(salgspris)
from ordrer
group by varenavn.

ok - her er min nye forespørgsel.

men den skal også hente alle poster i tabellen tblcd og tblcat.
samt at den skal sortere efter cdtitle...

strSQL = "SELECT avg(CDCommentStars), count(*) " 
strSQL = strSQL & "FROM tblCDComment, tblCD, tblCDCat "
strSQL = strSQL & "WHERE tblCD.CDCat = tblCDat.CDCatID "
strSQL = strSQL & "AND tblCDComment.CDCommentCDID = tblCD.CDID "
strSQL = strSQL & "GROUP BY CDCommentStars"

/haase

tilføj en ORDER BY efter GROUP BY og de felter du skal have ud fra de øvrige tabeller. Eneste ulempe er, at du kun kan få de felter (kolonner) ud i "tekst" som du vælger at gruppere på eller benytte aggrerede funktioner på (AVG, MAX, MIN etc).

strSQL = "SELECT avg(CDCommentStars), count(*) " 
strSQL = strSQL & "FROM tblCDComment, tblCD, tblCDCat "
strSQL = strSQL & "WHERE tblCD.CDCat = tblCDat.CDCatID "
strSQL = strSQL & "AND tblCDComment.CDCommentCDID = tblCD.CDID "
strSQL = strSQL & "GROUP BY CDCommentStars, cdcommentstars, cdtitle, cdid, cdcatid, cdcattitle, cdcommentid, cdcommentcdid, cdcoverfilesmall "
strSQL = strSQL & "ORDER BY CdTitle ASC"

men nu får jeg fejlen:

ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.

strSQL = "SELECT CDCommentStars, cdcommentstars, cdtitle, cdid, cdcatid, cdcattitle, cdcommentid, cdcommentcdid, cdcoverfilesmall, avg(CDCommentStars) as avgstars, count(*) as antal " 
strSQL = strSQL & "FROM tblCDComment, tblCD, tblCDCat "
strSQL = strSQL & "WHERE tblCD.CDCat = tblCDat.CDCatID "
strSQL = strSQL & "AND tblCDComment.CDCommentCDID = tblCD.CDID "
strSQL = strSQL & "GROUP BY CDCommentStars, cdcommentstars, cdtitle, cdid, cdcatid, cdcattitle, cdcommentid, cdcommentcdid, cdcoverfilesmall "
strSQL = strSQL & "ORDER BY CdTitle ASC"

jeps nu virker det, men den viser kun de poster hvori der er comments og der også dubletter...

strSQL = "SELECT CDCommentStars, cdtitle, cdid, cdcatid, cdcat, cdcattitle, cdcommentid, cdcommentcdid, cdcoverfilesmall, cdartist, avg(CDCommentStars) as avgstars, count(*) as antal " 
strSQL = strSQL & "FROM tblCDComment, tblCD, tblCDCat "
strSQL = strSQL & "WHERE tblCD.CDCat = tblCDCat.CDCatID "
strSQL = strSQL & "AND tblCDComment.CDCommentCDID = tblCD.CDID "
strSQL = strSQL & "GROUP BY CDCommentStars, cdtitle, cdcat, cdid, cdcatid, cdcattitle, cdcommentid, cdcommentcdid, cdcoverfilesmall, cdartist "
strSQL = strSQL & "ORDER BY CdTitle ASC"

kan man bruge DISTINCT?

Der er ikke dubletter i en GROUP BY - der vil være forskel i en af de kolonner du grupperer på.  At den kun viser de poster hvori der er comments kan hænge sammen med en inner join.

Du kan skrive din inner join om til en outer join for at få dine data ud. Nemmeste er at starte med at få en simpel select til at returnere de rækker du er interesseret i og efterfølgende skrive den om til en group by

step1:

select felt, felt, felt
from tabel1 left outer join tabel2
on fk=pk


step2:
select felt, felt, avg(felt), count(*)
from tabel1 left outer join tabel2
on fk=pk
group by felt, felt
order by felt

Hej igen,

Nu ser det såen her ud - men virker stadig ikke:

Error Type:
Microsoft JET Database Engine (0x80040E14)
Syntax error in FROM clause.


strSQL = "SELECT  tblCDComment.CDCommentStars, tblCD.cdtitle, tblCD.cdid, tblCDCat.cdcatid, tblCD.cdcat, tblCDCat.cdcattitle, tblCDComment.cdcommentid, tblCDComment.cdcommentcdid, tblCD.cdcoverfilesmall, tblCD.cdartist, avg(CDCommentStars) AS AVGStars, COUNT(*) AS Antal "
strSQL = strSQL & "FROM tblCDComment "
strSQL = strSQL & "LEFT OUTER JOIN tblCD, tblCDCat "
strSQL = strSQL & "ON tblCD.CDCat = tblCDCat.CDCatID "
strSQL = strSQL & "AND tblCDComment.CDCommentCDID = tblCD.CDID "
strSQL = strSQL & "GROUP BY tblCDComment.CDCommentStars, tblCD.cdtitle, tblCD.cdid, tblCDCat.cdcatid, tblCD.cdcat, tblCDCat.cdcattitle, tblCDComment.cdcommentid, tblCDComment.cdcommentcdid, tblCD.cdcoverfilesmall, tblCD.cdartist "
strSQL = strSQL & "ORDER BY tblCD.CdTitle ASC"

jeg har tjekket w3schools.com ud - men det hjælper ikke...

http://www.w3schools.com/sql/sql_join.asp

Kan du prøve at poste et eks på de data du har i tabellerne og hvad output du ønsker?

Ok så få du det her...

http://www.henriklutzen.dk/tblCD.gif
http://www.henriklutzen.dk/tblCDCat.gif
http://www.henriklutzen.dk/tblCDComment.gif
http://www.henriklutzen.dk/relations.gif

+ felttyper:

http://www.henriklutzen.dk/felttyper.gif

ønsket output

http://www.henriklutzen.dk/output.gif

den skal tælle hvor mange anmeldelser der er til pågældende cd
og beregne gennemsnittet af antal stjerner...

Jeg tror det nemmeste bliver at lave en subselect (inline view) der beregner dine gennemsnit etc - og så benytte den i en almindelig join.

Således

select *
from tblcd inner join
(select cdid, avg(cdcommentstars) as stars, count(*) as antal
from tblcd c left outer join tblcdcomment t
on cdid = cdcommentcdid
group by cdid) as rating
on tblcd.cdid = rating.cdid
inner join tblcat
on tblcd.cdcat = tblcat.cdcatid
order by tblcd.cdtitle

hmm - jeg kan ikke få det til at virke...

og hvorfor står der: rating.cdid

select *
from tblcd inner join
(select cdid, avg(cdcommentstars) as stars, count(*) as antal
from tblcd left outer join tblcdcomment
on cdid = cdcommentcdid
group by cdid) as rating
on tblcd.cdid = rating.cdid,
inner join tblcat
on tblcd.cdcat = tblcat.cdcatid
order by tblcd.cdtitle


jeg får denne fejlmeddelelse:

Microsoft JET Database Engine (0x80040E14)
Syntax error in FROM clause.