sql - 无法形成 SQL 连接查询

Question

我是 SQL 的新手，正在尝试编写查询来查找类似以下场景的逻辑:

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

所以在这 3 天里，提交了一些作品，一些失败了，一些成功了。

我想编写一个查询，返回每天的作品总数、成功的作品和失败的作品。

我肯定做错了，但这就是我的想法。

请指出错误并说明逻辑。

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;

Answer 1

在什么情况下使用聚合函数

  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