I am getting: \"You tried to execute a query that

问题:

My code is as follows:

SELECT Last, OrderLine.OrdID, OrdDate, SUM(Price*Qty)  AS total_price
    FROM ((Cus INNER JOIN Orders ON Cus.CID=Orders.CID)
        INNER JOIN OrderLine
            ON Orders.OrdID=OrderLine.OrdID)
        INNER JOIN ProdFabric
            ON OrderLine.PrID=ProdFabric.PrID   
            AND OrderLine.Fabric=ProdFabric.Fabric  
    GROUP BY Last  
    ORDER BY Last DESC, OrderLine.OrdID DESC;  

This code has been answered before, but vaguely. I was wondering where I am going wrong.

You tried to execute a query that does not include the specified expression 'OrdID' as part of an aggregate function.

Is the error message I keep getting, no matter what I change, it gives me this error. Yes I know, it is written as SQL-92, but how do I make this a legal function?

回答1:

For almost every DBMS (MySQL is the only exception I'm aware of, but there could be others), every column in a SELECT that is not aggregated needs to be in the GROUP BY clause. In the case of your query, that would be everything but the columns in the SUM():

SELECT Last, OrderLine.OrdID, OrdDate, SUM(Price*Qty)  AS total_price
...   
GROUP BY Last, OrderLine.OrdID, OrdDate  
ORDER BY Last DESC, OrderLine.OrdID DESC;  

回答2:

If you have to keep your GROUP BY intact (and not to add non-agreggated fields to the list) then you need to decide which values you will want for OrderLine.OrdID and OrdDate. For example, you may chose to have MAX or MIN of these values.

So it's either as bernie suggested GROUP BY Last, OrderLine.OrdID, OrdDate or something like this (if it makes sense for your business logic):

SELECT Last, MAX(OrderLine.OrdID), MAX(OrdDate), SUM(Price*Qty) AS total_price