CODEWITHSUNDEEP
mysqlsqlselect
ivan
ivan

Reputation: 297

Need to output records

select distinct type as vuln_type,
                (select count(distinct repoId) from vulns
                 where type = vuln_type) as cnt
from vulns;

repoId | type

1      | TYPE1
1      | TYPE2
2      | TYPE2
2      | TYPE2

Output I want to get

TYPE1 | 1
TYPE2 | 2

You understand what I want to do: if a type is exist in a repoId, we increase the number on 1 (the number of records isn't matter, means if there are a lot of records with the same repoId and type, it shouldn't affect on the result). But my query doesn't work (work infinitely), may someone fix it?

Upvotes: 1

Views: 37

Answers (1)

Mureinik
Mureinik

Reputation: 311808

Instead of having to execute a subquery for every type, you could use a group by clause. This clause breaks up the query result (and any aggregate calculation) per distinct value in the group by list:

SELECT   type AS vuln_type, COUNT(DISTINCT repoId) AS cnt
FROM     vulns
GROUP BY type

Upvotes: 1

Related Questions