CODEWITHSUNDEEP
sql-servert-sqlcount
Adam
Adam

Reputation: 6122

Count number of occurrences in table

I want to select the title column from [listings] and the number of occurrences that listing has in the [ads] table, based on the same userid value.

Result should look like:

[title] [cnt]  
Acme    5  
MSFT    10  
GOOG    7

etc

This doesn't work:

select 
    l.title,
    (COUNT (id) FROM ads WHERE ads.userid = l.userid) as cnt
from 
    listings l
INNER JOIN 
    ads a on a.userid = l.userid
WHERE 
    l.listingtype = 2

Upvotes: 2

Views: 2277

Answers (3)

podiluska
podiluska

Reputation: 51494

I think the query and other answers are over complex

select 
    l.title,
    COUNT (a.id) as cnt
from 
    listings l
INNER JOIN 
    ads a on a.userid = l.userid
WHERE 
    l.listingtype = 2
GROUP BY l.title

Upvotes: 7

marc_s
marc_s

Reputation: 754488

You need a SELECT in your subquery:

SELECT
    l.title,
    (SELECT COUNT (id) FROM ads WHERE ads.userid = l.userid) AS cnt
FROM 
    listings l
INNER JOIN 
    ads a on a.userid = l.userid
WHERE 
    l.listingtype = 2

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172448

You need a group by clause to achive it.

Something like this:-

select l.title,COUNT (id) 
(SELECT  COUNT (id) FROM ads WHERE ads.userid=l.userid) as cnt
from listings l
INNER JOIN ads a on a.userid=l.userid
WHERE l.listingtype=2
group by l.title

Upvotes: 2

Related Questions