Error with GroupBy Sql query asp.net

Question

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

A MySQL answer is generally not appropriate for an MS Access tag. — Gordon Linoff, Mar 20 '18 at 11:04

Pranay Rana · Answer 1 · 2018-03-20T10:59:11.043

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";

@DavidIzhaki - welcome.. https://meta.stackexchange.com/questions/5234/how-does-accepting-an-answer-work — Pranay Rana, Mar 20 '18 at 11:13

score 1 · Answer 2 · answered Mar 20 '18 at 11:06

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.

Error with GroupBy Sql query asp.net

2 Answers2