CODEWITHSUNDEEP
mysqlsql
vloubes
vloubes

Reputation: 315

Sum of count occurrences

I have following query:

SELECT num1
    -> from table4
    -> WHERE suffix IN ('mt', 'dn')
    -> GROUP BY num1
    -> HAVING COUNT(DISTINCT suffix) = 2;

That shows me following:

1563866656871111 
1563866656876839 
1563866656878888 
1563867854324841

How can I get just a number 4 instead?

Thank you for any help :)

Upvotes: 0

Views: 55

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

after your question modification it seems you need below

SELECT num1
FROM table4
WHERE suffix IN ('mt', 'dn')
GROUP BY num1
HAVING COUNT(DISTINCT suffix)!<>2

Upvotes: 0

Primit
Primit

Reputation: 865

If you want count of rows then you will get it with this. 

SELECT COUNT(x.num1)
    FROM (
        SELECT num1
        FROM table4
        WHERE suffix IN ('mt', 'dn')
        GROUP BY num1
        HAVING COUNT(DISTINCT suffix) = 2
    ) x

Upvotes: 0

GMB
GMB

Reputation: 222462

You could just wrap your existing query: 

SELECT COUNT(*)
FROM (
    SELECT num1
    FROM table4
    WHERE suffix IN ('mt', 'dn')
    GROUP BY num1
    HAVING COUNT(DISTINCT suffix) = 2
) x

Note: given your example data, this will return 3, not 4.

Upvotes: 1

Related Questions