CODEWITHSUNDEEP
sqldatabasems-access
user1534344
user1534344

Reputation: 23

Aggregate Function Query

I want to Find out details of the proposals that have been assessed with the highest amount of grant approved. There is some problem with the query, This is the error message. You tried to execute a query that does not include the specified expression 'Title' as an aggregate function in ms access. 

SELECT Proposal.ProposalID,  Proposal.Title,  Proposal.RequestedAmmount, Researcher.Name, MAX(Proposal.RequestedAmmount)
FROM Researcher, Proposal
WHERE Researcher.ResearcherID=Proposal.ResearcherIDSub
GROUP BY  Proposal.ProposalID ;

Thanks

Upvotes: 0

Views: 118

Answers (2)

Bohemian
Bohemian

Reputation: 425418

Your query has two problems:

  • syntactically the group by must list all non-aggregate columns
  • it's an inner join - meaning that there must exist proposals for the researcher to be returned; researchers without proposals will be excluded instead of having a NULL in the MAX() column

To address these points, change your query to

  • GROUP BY 1, 2, 3, 4 (the SQL standard allows group by columns to be referenced by both expression and column position - I find position to be clearer, easier and terser)
  • Use an outer join by employing the more modern JOIN syntax with the LEFT option

As follows:

SELECT
    Proposal.ProposalID,
    Proposal.Title,
    Proposal.RequestedAmmount,
    Researcher.Name,
    MAX(Proposal.RequestedAmmount)
FROM Researcher
LEFT JOIN Proposal ON Researcher.ResearcherID=Proposal.ResearcherIDSub
GROUP BY 1, 2, 3, 4;

Upvotes: 0

Kshitij
Kshitij

Reputation: 8634

In GROUP BY query you need to group all paramters which are not inside function. So in your case Proposal.Title, Proposal.RequestedAmmount, Researcher.Name also needs to be grouped.

You will need to re-look in your SELECT and GROUP BY statement. See if you really need fields - Proposal.Title, Proposal.RequestedAmmount, Researcher.Name. If you need them, than you need to group them too.

Upvotes: 2

Related Questions