开发者

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 )
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜