Conditional count when case = 1, DB2
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'
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
- 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
-1when 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