Tristan
Tristan

Reputation: 93

Joining 3 tables based on a count

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

Answers (2)

sagi
sagi

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

Asad Ali
Asad Ali

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

Related Questions