Tristan Tristan - 5 months ago 11
SQL Question

Don't know where to put the where in the subquery

I would like to make a

view
that find the top 5 highest quantity items from a characters inventory but also a column that shows how many other people also has the item.


  • CHARACTERS


    • CHARID PK


  • INVENTORY


    • ITEMID

    • CHARID

    • quantity


  • ITEM


    • ITEMID




This is what I got so far,

CREATE VIEW vwPopItems --Top 5 most popular items
AS
SELECT TOP 5 INVENTORY.itemID, CHARACTERS.charName,quantity,
(SELECT COUNT(*) FROM INVENTORY where INVENTORY.itemID =) as cnt
FROM INVENTORY
JOIN CHARACTERS
ON CHARACTERS.charID = INVENTORY.charID
WHERE INVENTORY.charID = 6
GO


It instead show
count
as all the records in that table without the
WHERE
so I need to make a
WHERE
where it only shows it for the specific item of the top 5 for the amount of characters that also has the item. know this what above is wrong but could you guys please show me what to do to fix it? :)

Answer

Just use COUNT(*) instead of a subquery. Then use TOP and ORDER BYto get theTOP` 5.

SELECT TOP(5)
    i.itemID,
    c.charName,
    i.quantity,
    COUNT(*) AS cnt
FROM INVENTORY i
INNER JOIN CHARACTERS c
    ON c.charID = i.charID
WHERE
    i.charID = 6
GROUP BY 
    i.itemID,
    c.charName,
    i.quantity,
ORDER BY cnt DESC