CODEWITHSUNDEEP
sql
CodeIt
CodeIt

Reputation: 73

SQL count across two tables

I want to return the rows in the Base table that have an inventory over a certain count.

Something like this, but having difficulties...

SELECT b 
FROM Base b, Inventory i
WHERE COUNT(b.brand = i.brand) > 2

This should be returned:

brand  | model |
----------------
ford   |  T    |
chevy  |  X    |

.

Base
----------------
brand  | model |
----------------
toyota |  R    |
ford   |  T    |
chevy  |  X    |

.

Inventory
---------
brand  |
---------
toyota |
ford   |
ford   |
ford   |
toyota |
chevy  |
chevy  |
chevy  |

Edit History

Upvotes: 0

Views: 69

Answers (4)

Pரதீப்
Pரதீப்

Reputation: 93754

Use group by and having clause to filter the brand having count >2 Try this.

select * from base b where exists(SELECT 1
FROM Inventory i
where b.brand=i.brand
group by brand
having COUNT(1) > 2  )

Upvotes: 1

jbutler483
jbutler483

Reputation: 24559

From your query, It looks like you're trying to do a join, and then a count.

Something like:

SELECT *
FROM Base b
INNER JOIN Inventory I
ON (b.brand = i.brand)
GROUP BY i.brand
HAVING COUNT(i.brand) > 2

An alternative (that I could think of), would be to use a nested select:

SELECT * 
FROM Base
WHERE brand IN (
        SELECT brand
        FROM Inventory
        GROUP BY brand
        HAVING COUNT(*) > 2
              )

Upvotes: 2

Fabien TheSolution
Fabien TheSolution

Reputation: 5050

Just a GROUP BY with an HAVING can do the trick. You can do something like this :

SQL Fiddle

SELECT b.*
FROM Inventory i
INNER JOIN base b 
ON b.brand = i.brand
GROUP BY i.brand
HAVING COUNT(i.brand) > 2

Results:

| BRAND | MODEL |
|-------|-------|
| chevy |     X |
|  ford |     T |

Upvotes: 1

jpw
jpw

Reputation: 44921

You can useGROUP BYto group the records and useHAVINGto filter the groups like this:

SELECT b.brand, b.model
FROM Base b
JOIN Inventory i 
ON b.brand = i.brand
GROUP BY b.brand, b.model 
HAVING COUNT(i.brand) > 2

Upvotes: 1

Related Questions