CODEWITHSUNDEEP
sql
Abduazim Sobitov
Abduazim Sobitov

Reputation: 61

simple query for new learner

Just need help for new sql learner.

I have two tables. PRODUCT, VENDOR. Product has Vendor_ID as an foreign key. I need to show vendor_name, vendor_phone and number of products from each vendor. I would be greatfull if you help me with simple explanation of query.

Upvotes: 0

Views: 44

Answers (1)

PSK
PSK

Reputation: 17943

You can do COUNT(*) using GROUP BY like following.

SELECT V.VendorId, V.Vendor_Name AS VendorName, V.Vendor_Phone, Count(*) [Count]
FROM Vendor V INNER JOIN Product P ON P.Vendor_ID = V.Vendor_ID 
GROUP BY  V.VendorId, V.Vendor_Name,V.Vendor_Phone

Or using sub query like following.

SELECT V.Vendor_Name AS VendorName, V.Vendor_Phone,
  (
   SELECT COUNT(*) FROM Product P WHERE P.Vendor_ID = V.Vendor_ID 
  ) AS [Count]
FROM Vendor V

You can also do it using ROW_NUMBER(), like following.

SELECT * FROM
(
    SELECT V.VendorId, V.Vendor_Name AS VendorName, V.Vendor_Phone, 
            ROW_NUMBER() OVER(PARTITION BY V.Vendor_ID  ORDER BY V.Vendor_ID) RN
            Count(*) OVER(PARTITION BY V.Vendor_ID  ORDER BY V.Vendor_ID) [Count]
    FROM Vendor V INNER JOIN Product P ON P.Vendor_ID = V.Vendor_ID 
) T
WHERE T.RN=1

Upvotes: 1

Related Questions