CODEWITHSUNDEEP
sqlsql-servert-sql
mch_dk
mch_dk

Reputation: 369

Count using like in SQL - how to?

I got a two tables

tblGoals
-name
-url
-siteID

tblUrlCollection
-url
-siteID

How do I count how many times every "url" from tblGoals is in tblUrlCollection using "url" and "siteID" as input?

Would like to use like or something so I can add wildcards to the end of the "url" input parameter.

Please help me out - thanks a lot.

Performance is not a big issue here - backend stuff.

Upvotes: 1

Views: 860

Answers (2)

Martin Smith
Martin Smith

Reputation: 453327

DECLARE @SiteId int
DECLARE @url varchar(100)
SET @url = 'http://stackoverflow.com%'
SET @SiteId = 10

SELECT g.url, COUNT(u.url) AS C
FROM tblGoals g
LEFT OUTER JOIN tblUrlCollection u
ON g.siteID = u.siteID AND g.url = u.url
WHERE g.url LIKE @url and g.Site=@SiteId
GROUP BY g.url

Upvotes: 2

spinon
spinon

Reputation: 10847

I think this should work:

SELECT tblGoals.url, COUNT(tblGoals.url)
FROM tblUrlCollection
INNER JOIN tblGoals ON tblGoals.url = tblUrlCollection.url AND tblGoals.siteID = tblUrlCollection.siteID
GROUP BY tblGoals.url

Upvotes: 1

Related Questions