I have this table that lacks a unique key and want to find out how many duplicate urls are associated with other indexes. Is possible to query this?

| URL      | index         |
| -------- | --------------|
| /page1   | 1             |
| /page1   | 1             |
| /page1   | 2             |
| /page2   | 1             |
| /page2   | 1             |
| /page3   | 1             |
| /page3   | 2             |
| /page3   | 2             |
| /page3   | 3             |

I want to return

| URL      | count         |
| -------- | --------------|
| /page1   | 2             |
| /page3   | 3             |

to essentially find out which indexs have a duplicate url associated with another index.

CodePudding user response：

A GROUP BY and a COUNT will in cobination with HAVING give your anted result

CREATE TABLE table1
    (`URL` varchar(8), `index` varchar(14))
;
    
INSERT INTO table1
    (`URL`, `index`)
VALUES

    ('/page1', '1'),
    ('/page1', '1'),
    ('/page1', '2'),
    ('/page2', '1'),
    ('/page2', '1'),
    ('/page3', '1'),
    ('/page3', '2'),
    ('/page3', '2'),
    ('/page3', '3')
;

Records: 9  Duplicates: 0  Warnings: 0

SELECT
 `URL`, COUNT(DISTINCT`index`) as count_
FROM table1
GROUP BY `URL`
HAVING count_ > 1

fiddle