Search code examples
sqlssms-2014

Query that counts rows for a extra column for a select

I would like to make a viww 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 vwTop5
 AS
SELECT TOP 5 INVENTORY.itemID, INVENTORY.charID,quantity,COUNT(SELECT * 
FROM INVENTORY)
FROM INVENTORY
WHERE INVENTORY.charID = 3

I know this what above is wrong but could you guys please show me what to do to fix it? :)

Solution

  • You were close :

    CREATE VIEW vwTop5
 AS
SELECT TOP 5 INVENTORY.itemID, INVENTORY.charID,quantity,
       (SELECT COUNT(*) FROM INVENTORY) as cnt
FROM INVENTORY
WHERE INVENTORY.charID =

    Like @FelixPamittan said, you need to add an ORDER BY clause to specify which TOP 5 records you want.