CODEWITHSUNDEEP
sqlsql-server
user2527039
user2527039

Reputation: 928

Retrieving rows based on another column's aggregate values in MS SQL

I am trying to write a query for MS SQL Server which returns the container_parent_ids that have quantity of 0:

Container_id | Container_name | container_parent_id | quantity | alive
----------------------------------------------------------------------
1            | ContA          | 12345               | 2        | 1
2            | ContB          | 12345               | 1        | 1
3            | ContC          | 12345               | 0        | 1
4            | ContD          | 12345               | 1        | 1
5            | ContA          | 23456               | 1        | 1
6            | ContB          | 23456               | 0        | 1
7            | ContC          | 23456               | 1        | 1
8            | ContD          | 34567               | 0        | 1
9            | ContE          | 34567               | 0        | 1

In the above data, only ContD and ContE should be returned for 34567.

If I group by container_parent_id and use WHERE quanity = 0, the result includes other rows.

This is what I have been trying: 

SELECT count(), container_parent_id from Container where quantity = 0 GROUP BY container_parent_id HAVING count() > 0

resulting in:

3 | ContC | 12345 | 0 | 1 
6 | ContB | 23456 | 0 | 1 
8 | ContD | 34567 | 0 | 1 
9 | ContE | 34567 | 0 | 1

Problem with running this query or what Renu/Stan suggested is, it returns those containers that have quantity as 0 - as a subset. But, I do not want the first two as they have other containers with quantities 1 or 2 for the same container_parent_id. My final output should just be:

8 | ContD | 34567 | 0 | 1 
9 | ContE | 34567 | 0 | 1

I.e. every container_parent_id that which has only records with quantity = 0.

Upvotes: 0

Views: 728

Answers (3)

huMpty duMpty
huMpty duMpty

Reputation: 14470

SELECT T.*
FROM Container  t
    INNER JOIN (      
            SELECT container_parent_id,SUM(quantity) Tot
            FROM Container   
            GROUP BY container_parent_id) g ON T.container_parent_id = G.container_parent_id
WHERE G.Tot=0

Upvotes: 1

ktdrv
ktdrv

Reputation: 3683

Something like this should work:

SELECT * 
FROM Containers
WHERE 
    container_parent_id IN (
        SELECT container_parent_id 
        FROM Containers 
        GROUP BY container_parent_id 
        HAVING sum(quantity) = 0
      );

First select all the parent IDs that have a total of 0 quantity and then use that as a nested query to get all the container names that have those IDs as parents.

Upvotes: 2

Renu
Renu

Reputation: 119

Simply Try this,

SELECT container_parent_id FROM Container WHERE quantity=0

Upvotes: -1

Related Questions