Getting aggregate sub-values in aggregate query

Question

I have a query that need to show aggregated values in an already aggregated outer query.

Consider a query that fetches total count of accounts being credited on a certain day.

I would like to display

1. total number orders
2. Total orders that are "PENDING"
3. Total orders that are "OPEN"

Here is the query that seems to make sense but as resultset show below, I am obviously not using the right approach. The "OPEN" column displays the entire total, the "PENDING" column stays NULL.

I feel I may be using the wrong approach, what are the options to have this work?

SELECT s.created, COUNT(s.id) 'total_accounts_credited', SUM(s.withdrawal) 'total_amount_credited', 
(select count(s.id) WHERE s.status_id = 'OPEN')  total_open_credited,
(select count(s.id) WHERE s.status_id = 'PENDING') total_pending_credited
  FROM statements s 
WHERE s.status_id in ('OPEN', 'PENDING')
GROUP BY 
  YEAR(s.created), 
  MONTH(s.created), 
  DAY(s.created) 
  ORDER BY s.created DESC
;

Results:

+---------------------+-------------------------+-----------------------+---------------------+----------------------+
| created             | total_accounts_credited | total_amount_credited | total_open_credited | total_pending_credited |
+---------------------+-------------------------+-----------------------+---------------------+----------------------+
| 2019-01-19 00:00:00 |                   18050 |              20813.18 |               18050 |                 NULL |
| 2019-01-12 00:00:00 |                   18135 |              24768.43 |               18135 |                 NULL |
| 2019-01-10 09:00:27 |                      80 |               1497.75 |                  80 |                 NULL |
| 2019-01-09 09:20:55 |                      51 |                933.50 |                  51 |                 NULL |
| 2019-01-08 16:45:14 |                      10 |                187.50 |                  10 |                 NULL |
| 2019-01-05 18:21:00 |                   17588 |              16968.49 |               17588 |                 NULL |
| 2018-12-29 00:00:00 |                   17893 |              25404.18 |               17893 |                 NULL |
| 2018-12-28 15:23:04 |                       1 |                 35.00 |                   1 |                 NULL |
| 2018-12-22 00:00:00 |                   17353 |              17048.18 |               17353 |                 NULL |
| 2018-12-15 00:00:00 |                   16893 |              10181.34 |               16893 |                 NULL |
| 2018-12-08 00:00:00 |                   16220 |              99547.09 |               16220 |                 NULL |
| 2018-12-01 00:00:00 |                   15476 |              87699.59 |               15476 |                 NULL |
+---------------------+-------------------------+-----------------------+---------------------+----------------------+

Answer 1

SELECT  ...,
        SUM(s.status_id = 'OPEN') AS total_open_credited,
        ...
    FROM ...
    WHERE ...
    GROUP BY DATE(s.created)   -- a simplification
    ORDER BY ...

Explanation: s.status_id = 'OPEN' is a boolean expression that evaluates to TRUE or FALSE. TRUE is 1; FALSE is 0. Then SUM correctly tallies what you asked for.