GROUP BY problem with varchar

Question

There are links stored in a DB as varchars. With these links I want to use GROUP BY.

http://example.com
http://example.com
http://example.com

SQL over that data:

SELECT COUNT(*) c, Url
    FROM Advertisements
    GROUP BY Url

I want this output:

c Url 
3 http://example.com

But instead I get this three times:

c Url 
1 http://example.com

Why doesn't SELECT group the varchar fields? They are the same but GROUP BY does not detect that. Any ideas?

Answer 1

If the string containing those URLS is the data that is stored, they are not the same url, each one is differnent therfore group by would put each ina differnt group.

Answer 2

The endings are different

7​i18704

5​i18704

4​i18704

Following your comment I have updated and they GROUP as expected. What do you get when you try this?

CREATE TABLE #Advertisements
(
ID INT IDENTITY(1,1),
Url VARCHAR(200)
)

INSERT INTO #Advertisements VALUES
('http://example.com')

INSERT INTO #Advertisements VALUES
('http://example.com')

INSERT INTO #Advertisements VALUES
('http://example.com')



SELECT COUNT(*) c, Url
    FROM #Advertisements
    GROUP BY Url

Answer 3

Just like HLGEM and Martin said, the whole text in the field has to be the same so that the GROUP BY works, you can use something like GROUP BY SUBSTRING(Url, 0, 30), this way you'll get:

URL | COUNT

http://example.com | 3