在mysql中使用groupby时如何获取条件计数？

1tuwyuhd 于 2021-07-24 发布在 Java

关注(0)|答案(1)|浏览(404)

我是新手。
我有一个表（名称：“audit\u webservice\u aua”），如下所示：

+---------+------------------------------------+-------------------+------------------------+
| auditId |          device_code               |   response_status |       request_date
+---------+------------------------------------+-------------------+------------------------+
|   10001 | 0007756-gyy66-4c6e-a59d-xxxccyyyt1 |       P           | 2020-03-02 00:00:08.785
|   10002 | 0007756-gyy66-4c6e-a59d-xxxccyyyt2 |       F           | 2020-04-06 00:00:08.785
|   10003 | 0007756-gyy66-4c6e-a59d-xxxccyyyt3 |       F           | 2020-04-01 00:01:08.785
|   10004 | 0007756-gyy66-4c6e-a59d-xxxccyyyt1 |       P           | 2020-05-02 00:02:08.785
|   10005 | 0007756-gyy66-4c6e-a59d-xxxccyyyt1 |       P           | 2020-05-09 00:03:08.785
|   10006 | 0007756-gyy66-4c6e-a59d-xxxccyyyt2 |       P           | 2020-05-09 01:00:08.785
|   10007 | 0007756-gyy66-4c6e-a59d-xxxccyyyt7 |       F           | 2020-06-06 02:00:08.785
+---------+------------------------------------+-------------------+------------------------+

每次发出新请求时，上表存储请求设备代码、响应状态和请求时间。
我需要得到一个结果集，其中包含每个设备的代码，总交易，总成功，总失败和两个给定日期之间每天的日期。
我写的问题如下：

SELECT DATE_FORMAT(aua.request_date,'%b') as month , 
    YEAR(aua.request_date) as year,
     DATE_FORMAT(aua.request_date,'%Y-%m-%d') as date,
    (select count(aua.audit_id) )as total_trans ,
    (select count(aua.audit_id) where aua.response_status  'P') as total_failure ,
    (select count(aua.audit_id) where aua.response_status = 'P') as total_successful ,
    aua.device_code as deviceCode
    FROM audit_webservice_aua aua where DATE_FORMAT(aua.request_date,'%Y-%m-%d') between '2020-04-16' and '2020-07-17' 
    group by dates,deviceCode ;

在上述代码中，我尝试获取介于“2020-03-02”和“2020-06-06”之间的结果，但我获取的计数不正确。任何帮助都将不胜感激。先谢谢你。

sql mysql

来源：https://stackoverflow.com/questions/63245721/how-get-conditional-count-while-using-group-by-in-mysql

1条答案

按热度按时间

zpgglvta1#

我想你只需要条件聚合：

SELECT DATE_FORMAT(aua.request_date,'%b') as month , 
       YEAR(aua.request_date) as year,
       DATE_FORMAT(aua.request_date, '%Y-%m-%d') as date,
       COUNT(aua.audit_id) as total_trans ,
       SUM(aua.response_status <> 'P') as total_failure,
       SUM(aua.response_status = 'P') as total_successful,
       aua.device_code as deviceCode
FROM audit_webservice_aua aua 
WHERE DATE_FORMAT(aua.request_date, '%Y-%m-%d') between '2020-04-16' and '2020-07-17' 
GROUP BY month, year, date, deviceCode ;

我也建议你换个房间 WHERE 条款至：

WHERE aua.request_date >= '2020-04-16' AND
      aua.request_date >= '2020-07-18'

赞(0）回复(0）举报 2021-07-24

我来回答

在mysql中使用groupby时如何获取条件计数？

1条答案

相关问题

热门标签

最新问答