SQL Query: How to show the opposite result of your query in select statement

Question

Im a lil bit stuck here, really hope somebody could help me out. I couldn't found yet the answer from google.

Ok let say the data is like below:

TableName=> Events

| Node_Name| Status    |
+----------+-----------+
| Node_1   | Completed |
| Node_1   | Completed |
| Node_2   | Failed    |
| Node_2   | Completed |
| Node_3   | Failed    |
| Node_3   | Failed    |

Query:

select node_name, count(*)
from events
where status='Failed'
group by node_name

Result:

| Node_2 | 1 |
| Node_3 | 2 |

The question is, how can i group a node_name that never "failed" and always completed.

Query: ?????????

Result that I wanted:

=>Node_1

Really appreciate your help.

Thank you!

Answer 1

In SQL Server, the EXCEPT operator can do this.

select node_name 
from events 
group by node_name

EXCEPT

select node_name
from events
where status<>'Completed' ;

Explanation: You query all different node names, and you remove all names which are not completed. As a result, only those will stay which are ONLY completed.

Answer 2

SELECT
    node_name
FROM
    events
WHERE
    node_name NOT IN (SELECT
                        node_name
                      FROM
                        events
                      WHERE
                        status = 'Failed')
GROUP BY
    node_name

To prevent havoc with missing result sets and jokesters adding Abbracadabra as a status you can use this. Patent Pending

WITH cteAbbracadabra AS
(
    SELECT
        node_name, status, RANK() OVER (PARTITION BY node_name ORDER BY status) rnk
    FROM
        events
    GROUP BY
        node_name, status
)
SELECT  node_name 
FROM    cteAbbracadabra c
WHERE   status = 'Completed'
        AND NOT EXISTS (
            SELECT  * 
            FROM    cteAbbracadabra 
            WHERE   node_name = c.node_name 
                    AND rnk <> c.rnk)

Answer 3

OR with NOT EXISTS...

http://sqlfiddle.com/#!6/60887/4

select Node_Name FROM events WHERE NOT EXISTS
(SELECT 1 FROM events e WHERE e.Node_Name=events.Node_Name AND Status='Failed')
GROUP BY Node_Name

Answer 4

If you're looking for all the statuses in one query, try this (I'm using SQL Server 2014):

SELECT
  [Node_Name],
  SUM(CASE WHEN [Status] = 'Failed' THEN 1 ELSE 0 END) AS Failures
FROM (VALUES 
  ('Node_1', 'Completed'),
  ('Node_1', 'Completed'),
  ('Node_2', 'Failed'),
  ('Node_2', 'Completed'),
  ('Node_3', 'Failed'),
  ('Node_3', 'Failed')
  ) x ([Node_Name], [Status])
GROUP BY [Node_Name]

Answer 5

To get only the "never failed" nodes:

select node_name
from events
group by node_name
having max(status) = 'Completed'

To get all nodes with a 'Failed' count:

select node_name, 
   sum(case when status = 'Failed' then 1 else 0 end )
from events
group by node_name

Answer 6

You could create a new column where you can assign a '0' value to completed ones and then everything else to 1. And, then grab only ones where the max() value = 0 (completed only) like this:

SELECT t.node_name
FROM (
    SELECT node_name
        ,STATUS
        ,CASE 
            WHEN STATUS = 'Completed'
                THEN 0
            ELSE 1
            END Completed_Flg
    FROM events
    ) t
GROUP BY t.node_name
HAVING max(t.completed_flg) = 0

SQL Fiddle Demo

Answer 7

select node_name, count(*)
from events
where count(*) = 
(SELECT count(*) from events where status = 'completed') 
group by node_name; 

that should work, so after doing some testing I found that my original solution didn't work. however I feel like I have now found a working model:

SELECT NODE_NAME, COUNT(*) 
FROM EVENTS 
GROUP BY NODE_NAME 
HAVING(NODE_NAME, COUNT(*)) IN 
(SELECT NODE_NAME, COUNT(*) 
FROM EVENTS WHERE
STATUS = 'completed' GROUP BY NODE_NAME);

so this gets it down to displaying node_1 2. the issue I've run into at this point is being able to drop the count from the end result. I attempted to wrap this into another subquery that I would only pull the node_name from using the in keyword, however because my subqueries thus far each had two operands it won't let me. I'll continue to try to find a finished working model, but this should help to push you in the right direction.

SELECT NODE_NAME FROM(
SELECT NODE_NAME, COUNT(*) 
FROM EVENTS GROUP BY NODE_NAME 
HAVING (NODE_NAME, COUNT(*)) 
IN (SELECT NODE_NAME, COUNT(*) 
FROM EVENTS where STATUS = 'completed' group by node_name)) as tabletest;

alright, I've managed to figure it out, was kind of silly of me not to realize this right away.

Answer 8

This should get you what you want

select Distinct Node_Name, COUNT(Node_Name) NodeCount
from events e
where e.Status <> 'Failed'
    AND e.Node_Name NOT IN
    (
        SELECT Node_Name
        FROM events e
        WHERE e.Status <> 'Completed'
    )
GROUP BY Node_Name

Returns:

Node_Name | NodeCount
Node_1    | 2

Answer 9

I have tried several solution were posted. Most of them working. But I only need the simplest one because the query is for Tivoli Storage Manager (TSM) that is running on IBM DB2.

I understand that there might be some flaws for simplest method, but I don't think TSM has such capability to accommodate complicated query.

Thank you for your time. I appreciate it. Sorry for late reply. I didn't know that the reply would be this fast. This is my first post and I am new to both SQL and TSM :)

Hence, after testing the SQL query in TSM Server. This working fine:

select node_name from events group by node_name having max(status) = 'Completed'