CODEWITHSUNDEEP
sqlsql-serverperformance
Thiago Augustus Oliveira
Thiago Augustus Oliveira

Reputation: 722

SQL Query with group and having

I'm trying to select all client ID's that has TypeId equal 1 but not TypeId equal 3.

Table example:

---------------------
| ClientID | TypeId |
---------------------
|     1    |    1   |
|     1    |    3   |
|     2    |    3   |
|     3    |    1   |
---------------------

My query:

SELECT ClientId, TypeId
FROM Table
GROUP BY ClientId, TypeId
HAVING TypeId != 3

What I have:

---------------------
| ClientID | TypeId |
---------------------
|     1    |    1   |
|     3    |    1   |
---------------------

What I expect:

---------------------
| ClientID | TypeId |
---------------------
|     3    |    1   |
---------------------

The critical thing is that the table have more than 3 * 10^8 registers.

Thanks in advance!

Upvotes: 2

Views: 149

Answers (4)

Neil Hibbert
Neil Hibbert

Reputation: 862

I think you could also achieve this with a common table expression:

WITH cte AS (
    SELECT ClientId
    FROM [Table]
    WHERE TypeId = 1
)
SELECT DISTINCT ClientId
FROM [Table]
WHERE ClientId IN
(
    SELECT ClientId
    FROM cte
)
AND TypeId != 3

Alternatively, try this:

 WITH cte AS (
        SELECT ClientId
        FROM [Table]
        WHERE TypeId = 1
 )
 SELECT ClientId
 FROM cte

 EXCEPT

 SELECT CLientId
 FROM [Table]
 WHERE TypeId = 3

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269633

I would suggest aggregation and having:

SELECT ClientId
FROM Table
GROUP BY ClientId
HAVING SUM(CASE WHEN TypeId = 1 THEN 1 ELSE 0 END) > 0 AND
       SUM(CASE WHEN TypeId = 3 THEN 1 ELSE 0 END) = 0;

Each condition in the HAVING clause counts the number of rows having a particular TypeId value. The > 0 means there is at least one. The = 0 means there are none.

If you actually want to get the original rows that match -- so all TypeIds associated with a client. You can use a JOIN or window functions:

SELECT ClientId, TypeId
FROM (SELECT ClientId, TypeId,
             SUM(CASE WHEN TypeId = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY ClientId) as TypeId_1,
             SUM(CASE WHEN TypeId = 3 THEN 1 ELSE 0 END) OVER (PARTITION BY ClientId) as TypeId_3
      FROM Table
     ) t
WHERE TypeId_1 > 0 AND TypeId_3 = 0;

Upvotes: 4

Katia
Katia

Reputation: 629

Try this one 

SELECT ClientId, TypeId
FROM Table
WHERE ClientId not in (select ClientId from Table where TypeId = 3)
GROUP BY ClientId, TypeId

Upvotes: 1

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

Try this:

SELECT t1.* 
FROM Table AS t1
WHERE TypeId = 1 AND 
      NOT EXISTS (SELECT 1
                  FROM Table AS t2
                  WHERE t1.ClientId = t2.ClientId AND t2.TypeId = 3)

Upvotes: 2

Related Questions