SQL - How to count items from other tables?
I have a table name BRANDS. I have another table named MODELS. The tables are like this:
BRANDS
BrandID // pk
BrandName
MODELS
ModelID // pk
BrandID // fk
ModelName
Each brand has zero to many brands associated with it. Each model can be associated with only one brand.
My output should look something like this:
Brand # of Models
Goodyear 5
Chevy 19
Toyota 7
The query I use to get the models looks like this:
SELECT BrandID, BrandName
FROM Brands
ORDER BY BrandName
开发者_运维百科
My count query looks like this:
SELECT COUNT(BrandID) AS TotalBrands
FROM Brands
WHERE SiteID = ?
I have a few other things that I need to count also, from other tables.
Can you get me started on how to count the items from the MODELS table?
Thanks!!!
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ ANSER
I used all of your answers as hints to help me get what I needed. This is the real query.
SELECT S.SiteID,
S.SiteName,
COUNT(BrandID) AS TotalBrands
FROM Sites S
LEFT JOIN Brands B
ON S.SiteID = B.SiteID
GROUP BY S.SiteID, S.SiteName
SELECT Brands.BrandID, COUNT(*)
FROM Brands INNER JOIN Models ON Brands.BrandID=Models.BrandID
GROUP BY Brands.BrandID
Something like this:
SELECT BrandID, COUNT(ModelID)
FROM BRANDS JOIN MODELS USING (BrandID)
GROUP BY BrandID
This will not include BRANDS with 0 MODELS. If you want them too, you'll need LEFT JOIN.
(Oracle syntax, you may need to use ON
instead of USING
for other databases)
SELECT B.BrandName AS 'Brand',
( SELECT COUNT(*) FROM Models AS M WHERE M.BrandID = B.BrandID ) as '# Of Models'
FROM Brands AS B
ORDER BY B.BrandName
This will include brands which have zero models. You can filter them out in a WHERE clause:
WHERE exists ( SELECT 42 FROM Models WHERE BrandID = B.BrandID )
精彩评论