CODEWITHSUNDEEP
sqlms-accessvba
emilios
emilios

Reputation: 375

Query syntax mistake

SELECT Sum(tblWarehouseItem.whiQty) AS Sum,
tblWarehouseItem.whiItemName
FROM tblWarehouseItem
WHERE tblWarehouseItem.whiActive =True 
AND tblWarehouseItem.whiCansel=False 
AND tblWarehouseItem.whiExpiryDate Is Null
GROUP BY tblWarehouseItem.whiItemName, tblWarehouseItem.whiQty

if you please help me i am confused...

i have the above Query.

For example in my table i have:

whiItemName   whiQty(sum)
Fuel          100
Fuel          100
Fuel          100

I wlould like the output to be:

whiItemName   whiQty(sum)
Fuel          300

Upvotes: 0

Views: 44

Answers (2)

John Babb
John Babb

Reputation: 951

The short of it is you need to remove the tblWarehouseItem.whiQty from the group by. I'm not sure what you are doing with the where condition but you also might try as a having keyword. The statement below will illustrate the point.

SELECT tblWarehouseItem.whiItemName, Sum(tblWarehouseItem.whiQty) AS Sum FROM tblWarehouseItem GROUP BY tblWarehouseItem.whiItemName

Upvotes: 0

paul
paul

Reputation: 22011

You have an extra column in your GROUP BY

You just need:

SELECT Sum(tblWarehouseItem.whiQty) AS Sum,
tblWarehouseItem.whiItemName
FROM tblWarehouseItem
WHERE tblWarehouseItem.whiActive =True 
AND tblWarehouseItem.whiCansel=False 
AND tblWarehouseItem.whiExpiryDate Is Null
GROUP BY tblWarehouseItem.whiItemName

Upvotes: 4

Related Questions