Conditional count when case = 1, DB2

db2 if statement
db2 case statement
db2 count greater than 1
how to count number of columns in a table in db2
db2 sql count multiple columns
db2 iif statement
db2 count table rows
db2 sql count null

I'm currently trying to figure out the best way to do a conditional count as an alias in DB2 for Iseries. The below values represent job statuses where a job can be created, completed and cancelled so any one job will possibly have multiple status codes attached to it.

However, for my final value, I'm trying to get a count of jobs that only have the created status so that I can show how many are still open jobs. Basically looking for cases where the count for the created case = 1, but the below fails at the '='

SELECT
  COUNT(CASE A1.JOB WHEN = 'CREATED' THEN 1 END) AS CREATED,
  COUNT(CASE A1.JOB WHEN = 'CANCELLED' THEN 1 END) AS CANCELLED,
  COUNT(CASE WHEN A1.JOB 'CREATED' = 1 then 1  END)  AS OPEN
FROM SCHEMA.TABLE A1;

sample data and results:

    Job ID   |   Status_code
-------------------------
123         'CREATED'
123         'COMPLETED'
521         'CREATED'
521         'CANCELLED'
645         'CREATED'

Results:

JOB  |  CREATED  |   CANCELLED   |   OPEN
-------------------------------------------
123     1               0               0
521     1               1               0
645     1               0               1

Assuming valid close status is either "COMPLETED" or "CANCELLED", you can try following SQL.

SELECT
  A1.JobID,
  sum(CASE  WHEN A1.Status_code = 'CREATED' THEN 1 ELSE 0 END) AS CREATED,
  sum(CASE  WHEN  A1.Status_code = 'CANCELLED' THEN 1 ELSE 0 END) AS CANCELLED,
  (
     SUM(CASE WHEN A1.Status_code = 'CREATED' THEN 1 ELSE 0 END)
   - sum(CASE WHEN A1.Status_code = 'CANCELLED' THEN 1 ELSE 0 END)
   - sum(CASE WHEN A1.Status_code = 'COMPLETED' THEN 1 ELSE 0 END)
 ) AS OPEN
FROM SCHEMA.TABLE A1
GROUP BY A1.JobID 

Can I have a CASE Statement in the WHERE Clause?, Can we use CASE statement in where clause in db2? Introduction to Db2 CASE expression. A CASE expression allows you to select an expression based on evaluation of one or more conditions. In other words, it allows you to add the if-else logic to your queries. Db2 supports two kinds of CASE expressions: simple CASE and searched CASE expressions.

Assuming that the only "close" status is 'CANCELLED', you can use not exists like this:

select count(*)
from schema.table t
where t.status_code = 'CREATED' and
      not exists (select 1
                  from schema.table t2
                  where t2.job = t.job and
                        t2.status_code in ('CANCELLED', 'COMPLETED', 'DELETED')
                 );

If you want multiple counts, then filtering like this does not work. So aggregate by job first:

select sum(is_created) as num_created,
       sum(is_cancelled) as num_cancelled,
       sum(is_created * (1 - is_cancelled) * (1 - is_completed) * (1 - is_deleted)) as open
from (select job,
             max(case when status_code = 'CREATED' then 1 else 0 end) as is_created,
             max(case when status_code = 'CANCELLED' then 1 else 0 end) as is_cancelled,
             max(case when status_code = 'COMPLETED' then 1 else 0 end) as is_completed,
             max(case when status_code = 'DELETED' then 1 else 0 end) as is_deleted
      from t
      group by job
     ) j

SQL COUNT() with HAVING, such as select_list, IN, WHERE, ORDER BY, and HAVING. Example 1 (simple-when-clause): Assume that in the EMPLOYEE table the first character of a department number represents the division in the organization. Use a CASE expression to list the full name of the division to which each employee belongs.

The following returns the result you need:

WITH TAB (Job_ID, JOB) AS 
(
VALUES
  (123, 'CREATED')
, (123, 'COMPLETED')
, (521, 'CREATED')
, (521, 'CANCELLED')
, (645, 'CREATED')
)
SELECT
  Job_ID
, COUNT(CASE A1.JOB WHEN 'CREATED'   THEN 1 END) AS CREATED
, COUNT(CASE A1.JOB WHEN 'CANCELLED' THEN 1 END) AS CANCELLED
, CASE 
    WHEN NULLIF(COUNT(1), 0) = COUNT(CASE A1.JOB WHEN 'CREATED' then 1 END) 
    THEN 1 
    ELSE 0 
  END AS OPEN
FROM TAB A1
GROUP BY JOB_ID;

Is ELSE 0 implied in my COUNT CASE WHEN statement , SELECT b.title, COUNT(a.author_id) author_count, CASE COUNT(a.author_id) WHEN 1 THEN 'Single Author' WHEN 2 THEN 'Two Authors' ELSE 'More Than  The UNIQUE keyword has the same meaning as the DISTINCT keyword in COUNT functions. The UNIQUE keyword instructs the database server to return the number of unique non-NULL values in the column or expression. The following example calls the COUNT UNIQUE function, but it is equivalent to the preceding example that calls the COUNT DISTINCT function:

With conditional aggregation:

SELECT
  JobID,
  MAX(CASE Status_code WHEN 'CREATED' THEN 1 ELSE 0 END) AS CREATED,
  MAX(CASE Status_code WHEN 'CANCELLED' THEN 1 ELSE 0 END) AS CANCELLED,
  MIN(CASE WHEN Status_code <> 'CREATED' THEN 0 ELSE 1 END) AS OPEN
FROM tablename 
GROUP BY JobID 

See the demo. Results:

> JobID | CREATED | CANCELLED | OPEN
> ----: | ------: | --------: | ---:
>   123 |       1 |         0 |    0
>   521 |       1 |         1 |    0
>   645 |       1 |         0 |    1

SQL COUNT() with DISTINCT, Instead of COUNT(*), we are using here SUM() with condition iside: sum(case when gmbill = 'Y' then 1 else 0 end). That way we can group by  BO: BI 4.0, 4.1 | XI 3, 3.1 | XI r2 | 6.x | 5.x DB: Oracle, MS SQL Server, DB2, Teradata, Netezza HW: Win, Linux and AIX servers www.perpendulum.com Latest Blog Posts • 2013-07-16 Number of reports in a WebI document • 2013-04-01 Optional prompts in a universe • 2012-06-08 Calendar table script for Oracle

Db2 CASE Expressions: Adding If-Else Logic to Queries in Db2, Use the HAVING clause to apply one or more qualifying conditions to groups or to In the following examples, each condition compares one calculated property of FROM cust_calls GROUP BY 1, 2 HAVING COUNT(*) > 1; Скопировать код. The HAVING clause with SQL COUNT() function can be used to set a condition with the select statement. The HAVING clause is used instead of WHERE clause with SQL COUNT() function. The GROUP BY with HAVING clause retrieves the result for a specific group of a column, which matches the condition specified in the HAVING clause.

SQL DB2: How to count multiple conditions on the same line , A CASE expression allows an expression to be selected based on the evaluation of one or more conditions. WHEN search-condition THEN result-expression NULL Example 1 (simple-when-clause): Assume that in the EMPLOYEE table the first In the first query, Db2 performs the division before performing the CASE  DB2 Application Programming and SQL Guide also describes how SQLWARN6 is set. Similarly, if a duration of months is added or subtracted, only months and, if necessary, years are affected. The day portion of the date is unchanged unless the result would be invalid (September 31, for example).

HAVING Clause, DB2: --#SET TERMINATOR @ CREATE OR REPLACE FUNCTION fn_color_exists(p_name VARCHAR(70)) RETURNS CHAR(1) BEGIN -- Execute the query  The sum of those rows will equal the number of rows where the condition was true — in this case, the number of premium customers! Meanwhile, since there’s no where clause on the whole query, we can still use count(1) for the total customer count. Now we can compare not only absolute numbers, but the growth rate of each customer segment over

Comments
  • Sample data and desired results would help.
  • @GordonLinoff sorry, just added it now
  • Thank you! This is the only option that gave me correct data so I accepted it
  • @TomN. what do you mean only option?
  • @TomN. you accepted an answer that is wrong because it would return -1 when there is competed, cancelled and created for the same jobid: dbfiddle.uk/…
  • @forpas good point, but it's the only solution that gave me appropriate data but there shouldn't be those cases. WOuld there be a good way to fix the -1 issue?
  • it's the only solution that gave me appropriate data what are you talking about? I posted a fiddle demo in my answer where you can see that it returns correct results.
  • Sorry I was trying to better clarify my question, but technically they could be completed, cancelled, or deleted as a closing status
  • but for this specific query I'm only trying to display created, cancelled and open