CODEWITHSUNDEEP
sqlpostgresql
StrugglingCoder
StrugglingCoder

Reputation: 5021

Not able to form SQL Join query

I am pretty new to SQL and am trying to write a query to find the logic for something like the following scenario:

wk_id | start_date | end_date | status

-----   ----------    ------    ------ 
1       20160101     20160101     SUCCEEDED
2       20160101     20160101     SUCCEEDED
3       20160101     20160101     SUCCEEDED
4       20160101     20160101     SUCCEEDED
5       20160101     20160101     FAILED
6       20160102     20160102     SUCCEEDED
7       20160102     20160102     SUCCEEDED
8       20160102     20160102     FAILED
9       20160102     20160102     FAILED
10      20160101     20160101     FAILED
11      20160101     20160101     FAILED

So for 3 days there are a few works submitted, a few have failed and a few have succeeded.

I want to write a query which returns the total number of works, succeeded works and failed works per each day.

I am definitely doing this wrong but this is what comes to my mind.

Please point out the errors and explain the logic.

select 
       w1.end_date,
       w1.status,COUNT(*) as total_instances, 
       COUNT(*) as as succeeded_instances,
       COUNT(*) as as failed_instances 
from 
       work_instances w1 , 
       work_instances w2
 where 
       w1.status LIKE 'SUCCEEDED' 
       and w2.status NOT LIKE 'SUCCEEDED' 
       and w1.wk_id = w2.wk_id 
 group by 
       w1.end_date,w1.status;

Upvotes: 0

Views: 50

Answers (3)

Michel Milezzi
Michel Milezzi

Reputation: 11115

Perhaps a more elegant way is using a FILTER in an aggregate function call:

WITH work_instances(wk_id, start_date, end_date, status) AS (
    VALUES 
        (1, '20160101'::DATE, '20160101'::DATE, 'SUCCEEDED'),
        (2, '20160101', '20160101', 'SUCCEEDED'),
        (3, '20160101', '20160101', 'SUCCEEDED'),
        (4, '20160101', '20160101', 'SUCCEEDED'),
        (5, '20160101', '20160101', 'FAILED'),
        (6, '20160102', '20160102', 'SUCCEEDED'),
        (7, '20160102', '20160102', 'SUCCEEDED'),
        (8, '20160102', '20160102', 'FAILED'),
        (9, '20160102', '20160102', 'FAILED'),
        (10, '20160101', '20160101', 'FAILED'),
        (11, '20160101', '20160101', 'FAILED')      
)
SELECT 
    end_date,
    count(*) AS total_instances,
    count(*) FILTER (WHERE status = 'SUCCEEDED') AS succeeded_instances,
    count(*) FILTER (WHERE status = 'FAILED') AS failed_instances        
FROM 
    work_instances
GROUP BY 
    end_date

Upvotes: 1

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use aggregate function with case when 

  select 
  w1.end_date,COUNT(*) AS total_work,
  sum(case when w1.status='SUCCEEDED' then 1 else 0 end ) as SUCCEEDED_task, 
  sum(case when w1.status='FAILED' then 1 else 0 end ) as FAILED_task                  
  from 
  work_instances w1 group by w1.end_date

Upvotes: 3

sticky bit
sticky bit

Reputation: 37472

Sounds like you could use conditional aggregation.

SELECT end_date,
       count(*) total_instances,
       count(CASE
               WHEN status = 'SUCCEEDED' THEN
                 1
             END) succeeded_instances,
       count(CASE
               WHEN status = 'FAILED' THEN
                 1
             END) failed_instances
       FROM work_instances
       GROUP BY end_date;

The CASE will only return a non null value (here it's 1, but it could be anything not null), if the status is the right one. And the count() doesn't count nulls only non null values.

Upvotes: 0

Related Questions