Apache Derby grouping or aggregate error:

Question

I have following problem with a Apache Derby problem (with subquery and inner join):

SELECT * FROM (SELECT ID,ENTRYDATE,CAST((SUM(DRUGAMOUNT_ML)) AS DECIMAL(15,2)) AS SUM_DRUGAMOUNT_ML FROM PORPHYRIE GROUP BY ENTRYDATE) a 
INNER JOIN PORPHYRIE b
ON a.ID=b.ID
;

The table PORPHYRIE looks like:

  ID   EntryDate   DRUGAMOUNT_ML DRUGAMOUNT_MG ....

  6601 2014-11-15  0.25 ...
  6602 2014-11-15  0.26 ...
              .
              .
              .

The error message is:

Column reference 'PORPHYRIE.ID' is invalid. For a SELECT list with a GROUP BY, the list may only contain grouping columns and valid aggregate expressions.

I do not understand what the problem is....please help!

Answer 1

I solved my problem by myself:

The correct solution is:

SELECT * FROM (SELECT ENTRYDATE,CAST((SUM(DRUGAMOUNT_ML)) AS DECIMAL(15,2)) AS SUM_DRUGAMOUNT_ML FROM PORPHYRIE GROUP BY ENTRYDATE) a 
INNER JOIN PORPHYRIE b
ON a.ENTRYDATE=b.ENTRYDATE
;

If you do a group- or aggregate function in derby ALL COLUMNS have to be part of the grouping or aggregate-action. Therefore the ID-colum in the subquery has to eliminated and the inner joining has to happen over the ENTRYDATES-Columns of both tables.

I do not think that this is a very vclever solution.