Reputation: 93
There is 3 Tables
ITEM -itemID
2.INVENTORY
-quantity
3.CHARACTERS
-charID
ITEM 1---many INVENTORY 8----1 CHARACTER
I want to find the top 10 most stacked items that is stacked in the characters inventory and in who has this item stacked in their inventory. So in the end it will be something like itemID 1 has 4 stacks and these charID has the item, itemID 2 has 3 stacks and these charID has the item. In a table.
This is where I am currently at after trying to do this for a while
SELECT COUNT(INVENTORY.itemID) as Stacks, ITEM.itemID, CHARACTERS.charName
FROM INVENTORY
INNER JOIN ITEM
ON INVENTORY.itemID = ITEM.itemID
JOIN CHARACTERS
ON INVENTORY.charID = CHARACTERS.charID
WHERE INVENTORY.quantity>1
GROUP BY ITEM.itemID
ORDER BY Stacks DESC
GO
I am getting a aggregate error and I dont know how to fix this query
Column 'CHARACTERS.charName' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Any help is welcome Thanks :). If my format of my question is not up to par please leave a comment of how I can improve.
Upvotes: 2
Views: 75
Reputation: 40481
Just add charName
to the GROUP BY
clause or wrap it with an aggregation function :
SELECT COUNT(INVENTORY.itemID) as Stacks, ITEM.itemID, MAX(CHARACTERS.charName)
FROM INVENTORY
INNER JOIN ITEM
ON INVENTORY.itemID = ITEM.itemID
JOIN CHARACTERS
ON INVENTORY.charID = CHARACTERS.charID
WHERE INVENTORY.quantity>1
GROUP BY ITEM.itemID
ORDER BY Stacks DESC
GO
To my knowledge, only MySQL accepts the GROUP BY
to not contain the entire select set of columns. Each column must be either in the GROUP BY
clause, or wrapped with an aggregation function like COUNT/MAX/MIN..
Upvotes: 2
Reputation: 361
Try Adding 'CHARACTERS.charName' in group by
clause also.
your final query should be like:
SELECT COUNT(INVENTORY.itemID) as Stacks, ITEM.itemID, CHARACTERS.charName
FROM INVENTORY
INNER JOIN ITEM
ON INVENTORY.itemID = ITEM.itemID
JOIN CHARACTERS
ON INVENTORY.charID = CHARACTERS.charID
WHERE INVENTORY.quantity>1
GROUP BY ITEM.itemID, CHARACTERS.charName
ORDER BY Stacks DESC
GO
Upvotes: 0