CODEWITHSUNDEEP
sqlms-access
David Izhaki
David Izhaki

Reputation: 23

Error with GroupBy Sql query asp.net

The query I'm trying to do is query that taking from OrderDetails all the items that accounts bought sum them for one row and show for me how much bought from any product, what i am tring to do it is to show the itemname and the item Image not only the id and sum this is the query that i did:

string strSql = "SELECT Sum(OrderDetails.ItemStock),OrderDetails.ItemID,Items.ItemName,Items.ItemImage FROM OrderDetails,Items WHERE OrderDetails.ItemID = Items.ItemID GROUP BY OrderDetails.ItemID ORDER BY SUM(OrderDetails.ItemStock) DESC";

the error that i got from this query : "The query does not include the specified phrase 'ItemName' as part of an aggregate function."

when i'm writing the query like this : 

  string strSql = "SELECT Sum(OrderDetails.ItemStock),OrderDetails.ItemID FROM OrderDetails GROUP BY OrderDetails.ItemID ORDER BY SUM(OrderDetails.ItemStock) DESC";

it is ok

Upvotes: 0

Views: 54

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Learn to use explicit JOIN syntax! And, fix your aggregation as well:

SELECT SUM(od.ItemStock), od.ItemID, i.ItemName, i.ItemImage
FROM OrderDetails as od INNER JOIN
     Items as i
     ON od.ItemID = i.ItemID
GROUP BY od.ItemID, i.ItemName, i.ItemImage
ORDER BY SUM(od.ItemStock) DESC;

The fix to your problem is simply to include all the non-aggregated columns in the GROUP BY. But you should learn how to write clean SQL along the way.

Upvotes: 1

Pranay Rana
Pranay Rana

Reputation: 176906

Error message "The query does not include the specified phrase 'ItemName' as part of an aggregate function." clearly says that you need to include column ItemName which is missing in your group by clause.

based on your columns in select statement you query should be as below , which will be all columns on which aggregate function not applied include in group by clause. that is requirement of group by clause in sql.

string strSql = "SELECT Sum(OrderDetails.ItemStock),
    OrderDetails.ItemID,Items.ItemName,Items.ItemImage
    FROM OrderDetails,Items
    WHERE OrderDetails.ItemID = Items.ItemID
    GROUP BY OrderDetails.ItemID,,Items.ItemName,Items.ItemImage
        ORDER BY SUM(OrderDetails.ItemStock) DESC";

Upvotes: 3

Related Questions