FabianE FabianE - 23 days ago 12
SQL Question

Microsoft Acces throwing an error msg while trying to execute an SQL SELECT

Hello everybody i hope you guys can help me, i have following problem:

When i try to run this SQL SELECT statement in Microsoft Acces i get an error message:

SELECT (tblBestelldetails.Einzelpreis * tblBestelldetails.Anzahl) AS Barwert
, tblKunden.Firma, Count(tbl.BestellungID)
FROM (tblBestellungen
INNER JOIN tblBestelldetails
ON tblBestellungen.BestellungID = tblBestelldetails.BestellungID)
INNER JOIN tblKunden
ON tblBestellungen.KundeID = tblKunden.KundeID;


The thing is it only starts to fail once i add the
Count(tbl.BestellungID)
parameter to select (I know i would have to group it by
BestellungID
later on but i tried that and it didn't fix my issue)

The weird thing is once I add the
Count(tbl.BestellungID)
it says that


the parameter (tblBestelldetails.Einzelpreis * tblBestelldetails.Anzahl) AS Barwert doesn't work because it isn't a aggregation function.


Does anybody know how i can fix this?

Answer Source

You need to group by all non-aggregated fields.

(tblBestelldetails.Einzelpreis * tblBestelldetails.Anzahl) isn't an aggregate unless you add a sum (or other aggregate), so you have to group by the same formula that is in the select. One can not use the alias in the aggregation as the alias is not know at the time the group by is evaluated by the DB engine. So you need to include the formula in the group by.

SELECT (tblBestelldetails.Einzelpreis * tblBestelldetails.Anzahl) AS Barwert
     , tblKunden.Firma
     , Count(tblBestellungen .BestellungID) 
FROM (tblBestellungen 
INNER JOIN tblBestelldetails 
   ON tblBestellungen.BestellungID = tblBestelldetails.BestellungID) 
INNER JOIN tblKunden 
   ON tblBestellungen.KundeID = tblKunden.KundeID
GROUP BY (tblBestelldetails.Einzelpreis * tblBestelldetails.Anzahl)
       , tblKunden.Firma

Perhaps you need to aggregate barwert as well.. It looks like you have a 1-M-1 relationship between tblBestellungen-tblBestelldetails - tblKunden.

So maybe we just need to aggregate the details and group by firma.

SELECT sum(tblBestelldetails.Einzelpreis * tblBestelldetails.Anzahl) AS Barwert
     , tblKunden.Firma
     , Count(tblBestellungen.BestellungID) 
FROM (tblBestellungen 
INNER JOIN tblBestelldetails 
   ON tblBestellungen.BestellungID = tblBestelldetails.BestellungID) 
INNER JOIN tblKunden 
   ON tblBestellungen.KundeID = tblKunden.KundeID
GROUP BY tblKunden.Firma

Or.... maybe you're not enforcing db integrity and need to distinct the IDs?

SELECT (tblBestelldetails.Einzelpreis * tblBestelldetails.Anzahl) AS Barwert
     , tblKunden.Firma
     , Count(distinct tblBestellungen.BestellungID) 
FROM (tblBestellungen 
INNER JOIN tblBestelldetails 
   ON tblBestellungen.BestellungID = tblBestelldetails.BestellungID) 
INNER JOIN tblKunden 
   ON tblBestellungen.KundeID = tblKunden.KundeID
GROUP BY (tblBestelldetails.Einzelpreis * tblBestelldetails.Anzahl)
       ,  tblKunden.Firma