I'm trying to construct a query that selects a groupname and the name of the CD with the highest sales. The schema looks something like
group(groupName, groupCode)
cd(cdTitle, numberSold, groupCode)
I have constructed a query like this:
SELECT mg.groupName, cd.cdTitle, MAX(cd.numberSold) as maxSold
FROM musicalGroup mg, cd cd
WHERE cd.groupCode = mg.groupCode
GROUP BY mg.groupCode
But I am receiving the error
ERROR: column "cd.cdtitle" must appear in the GROUP BY clause or be used in an aggregate function
If I remove the cd.cdtitle from the SELECT statement I am able to get the proper results that display
groupname... maxsold
"Test"... 80000
"Test2"... 81000
And so forth. Is there any way for me to also retrieve the CD name from this query?