CODEWITHSUNDEEP
sqlt-sqlaggregation
Atmocreations
Atmocreations

Reputation: 10061

COUNT() over result of Except

I have a query like:

SELECT
    id
FROM
    table1
INNER JOIN
    ...
WHERE
    ...
    AND eventtype IN (2000120, 2000121, 2000122, 2000123, 2000130)

EXCEPT

SELECT
    id
FROM
    table1
INNER JOIN
    ...
WHERE
    ...
    AND eventtype IN (2000123,2000130,2000134,2000135)

Note that the queries are both identical except the last where-clause.

I would now like to count the number of records returned by this above query.

How to?

Many thanks

Upvotes: 3

Views: 2845

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Also you can use EXISTS with INTERSECT sub-query

SELECT COUNT(ID)
FROM table1 t1 INNER JOIN ...
WHERE ... AND eventtype IN (2000120, 2000121, 2000122, 2000123, 2000130)
AND NOT EXISTS (
SELECT 1
FROM table1 t2 INNER JOIN ...
WHERE ... AND eventtype IN (2000123, 2000130, 2000134, 2000135)
AND EXISTS (SELECT t1.ID 
            INTERSECT
            SELECT t2.ID))

Upvotes: 0

Rednaxel
Rednaxel

Reputation: 968

you just need the first query for what you want, you don't have to include the 2000130 value if you don't need it and so the other values that you don't want to be shown in you query.

SELECT
    id
FROM
    table1
INNER JOIN
    ...
WHERE
    ...
    AND eventtype IN (2000120, 2000121, 2000122, 2000123)

If you need to exclude some values from the some query you can use

    AND eventtype NOT IN (2000130)

But not both at once. And to count just a regular use SELECT COUNT(id) or a subquery if you want.

Upvotes: 1

John Woo
John Woo

Reputation: 263723

use COUNT and wrap it inside a SUBQUERY

SELECT COUNT(ID)
FROM (
        SELECT id
        FROM table1
        INNER JOIN...
        WHERE...
                AND eventtype IN ( 2000120, 2000121, 2000122, 2000123, 2000130 )
        EXCEPT
        SELECT id
        FROM table1
        INNER JOIN...
        WHERE...
                AND eventtype IN ( 2000123, 2000130, 2000134, 2000135 )
        ) s

Upvotes: 5

Related Questions