count values with yes and not in sql statement

Question

I have a table with data like this:

 USER  NAME     Timestamp               EXECUTED    CLOSED
1 user1 JOHN DOE 2015-11-23 12:50:45         Yes       Yes
2 user1 JOHN DOE 2015-11-23 12:52:35         Yes       Yes
3 user1 JOHN DOE 2015-11-23 12:53:52         Yes       Yes
4 user1 JOHN DOE 2015-11-23 12:54:59         Yes       Yes
5 user1 JOHN DOE 2015-11-23 12:56:04         Yes       Yes
6 user1 JOHN DOE 2015-11-23 18:09:37         Yes       No
7 user1 JOHN DOE 2015-11-23 18:15:46         Yes       Yes
8 user1 JOHN DOE 2015-11-23 18:17:02         Yes       Yes

And i want to prepare a report like this:

USER   NAME      EXECUTED  CLOSED  NOTCLOSED
user1  JOHN DOE    8         7         1

I tried with something like this:

SELECT USER, NAME, Timestamp, count(EXECUTED), count(CLOSED) 
FROM table 
WHERE Timestamp LIKE "2015-11-23%" 
AND EXECUTED = "Yes" 
AND CLOSED = "Yes"

But the result in this case ever shows:

EXECUTED    CLOSED
8           8

When i have 1 record with Closed = No. I know that when i count the columns EXECUTED and CLOSED i can't eval properly with a WHERE = YES/NO because the datatype change to integer, but i can't find a proper solution.

Other thing: How i can show the proper value to "NO CLOSE" column? (difference between EXECUTED and CLOSED)

score 3 · Accepted Answer · answered Dec 02 '15 at 07:48

3

select user, name,
    sum(case when executed = 'Yes' then 1 else 0 end) as executed,
    sum(case when closed = 'Yes' then 1 else 0 end) as closed,
    sum(case when executed = 'Yes' and closed = 'No' then 1 else 0 end) as notclosed
from user
group by user, name

answered Dec 02 '15 at 07:48

Arvo

10,349
1
31
34

Works perfectly! Thanks!! – chan go Dec 02 '15 at 12:30

Michael L. · Answer 2 · 2015-12-02T16:14:37.473

-1

Something like this should work:

;WITH counts AS (
    SELECT USER
          ,NAME
          ,Timestamp
          ,SUM(CASE WHEN EXECUTED = "YES" THEN 1 ELSE 0 END) AS NumExecuted
          ,SUM(CASE WHEN CLOSED = "YES" THEN 1 ELSE 0 END) AS NumClosed
    FROM table 
    WHERE Timestamp LIKE "2015-11-23%" 
)
SELECT USER
      ,NAME
      ,Timestamp
      ,NumExecuted
      ,NumClosed
      ,NumExecuted - NumClosed AS NumOpen
FROM counts

edited Dec 02 '15 at 16:14

answered Dec 01 '15 at 21:59

Michael L.

620
3
17

Why are you putting a semicolon in front of the WITH? – CL. Dec 02 '15 at 07:27
1

COUNT() counts all non-NULL values, so it will happily count the `0` values, too. You'd need NULLs, or a different aggregation function. – CL. Dec 02 '15 at 07:31
@CL Yes, of course. I meant SUM, not COUNT (edited). I added the semicolon out of habit to make sure any previous statements are terminated before starting the CTE (common table expression). Completely unnecessary in this example, though. – Michael L. Dec 02 '15 at 16:15
Then why leave it there (and omit the terminating semicolon)? – CL. Dec 02 '15 at 21:35
See this response to the same question by Aaron Bertrand: http://dba.stackexchange.com/a/23374 – Michael L. Dec 03 '15 at 16:22

count values with yes and not in sql statement

2 Answers2