Postgres 9.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE phrases (
    id int,
    phrase text,
    count int
);

INSERT INTO phrases VALUES
(24, 'que', 2),
(24, 'querie', 1),
(24, 'queries', 1),
(25, 'term1', 3),
(25, 'term1+term2+term3', 1),
(25, 'term1+term2', 11),
(26, 'inventory', 5),
(26, 'issues', 10),
(27, 'close', 1),
(27, 'sclosed', 2),
(28, 'abcde', 2),
(28, 'abcfe', 2);

SELECT * FROM phrases;


12 rows affected



id
phrase
count


24
que
2

24
querie
1

24
queries
1

25
term1
3

25
term1+term2+term3
1

25
term1+term2
11

26
inventory
5

26
issues
10

27
close
1

27
sclosed
2

28
abcde
2

28
abcfe
2


SELECT 
    *, 
    SUM(is_diff) OVER (ORDER BY id, phrase) 
FROM (
    SELECT 
        *,
        CASE WHEN phrase LIKE (lag(phrase) over (order by id, phrase)) || '%' THEN 0 ELSE 1 END as is_diff
    FROM phrases 
)s


id
phrase
count
is_diff
sum


24
que
2
1
1

24
querie
1
0
1

24
queries
1
0
1

25
term1
3
1
2

25
term1+term2
11
0
2

25
term1+term2+term3
1
0
2

26
inventory
5
1
3

26
issues
10
1
4

27
close
1
1
5

27
sclosed
2
1
6

28
abcde
2
1
7

28
abcfe
2
1
8


SELECT 
    max(id) as id,
    max(phrase) as phrase,
    sum("count") as count
FROM (
    SELECT 
        *, 
        SUM(is_diff) OVER (ORDER BY id, phrase) as ranked
    FROM (
        SELECT 
            *,
            CASE WHEN phrase LIKE (lag(phrase) over (order by id, phrase)) || '%' THEN 0 ELSE 1 END as is_diff
        FROM phrases 
    )s
) s
GROUP BY ranked
ORDER BY ranked


id
phrase
count


24
queries
4

25
term1+term2+term3
15

26
inventory
5

26
issues
10

27
close
1

27
sclosed
2

28
abcde
2

28
abcfe
2

id	phrase	count
24	que	2
24	querie	1
24	queries	1
25	term1	3
25	term1+term2+term3	1
25	term1+term2	11
26	inventory	5
26	issues	10
27	close	1
27	sclosed	2
28	abcde	2
28	abcfe	2