Error in SQL SUM in QueryBuilder -SQL Server

Question

I get this error "Cannot call methods on int" when I try and execute the following sql statement in MVS datasource querybuilder.

SELECT
    item_k.ItemID,
    item_k.Name AS Expr1,
    SUM(item_k.Price) AS TotalPrice
FROM
    item_k
    INNER JOIN orderitems_k ON item_k.ItemID = orderitems_k.ItemID
GROUP BY
    item_k.Name

what seems to be the issue here?

thank you

Answer 1

Well, the first thing I can see is that you probably need to group by item_k.ItemID, and use comma (not period) to separate items in the SELECT clause (you have item_k.ItemID.item_k.Name:

SELECT item_k.ItemID, item_k.Name AS Expr1, SUM(item_k.Price) AS TotalPrice
FROM item_k
INNER JOIN orderitems_k
     ON item_k.ItemID = orderitems_k.ItemID
GROUP BY item_k.ItemID, item_k.Name

I wonder if the query shouldn't involve orderitems_k at some point too, though (hard to tell without your schema...)

Answer 2

By any chance, do you have a column in any of those tables that is named SUM?