Xrhrft

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 |

+---------------------+-------------------------+-----------------------+---------------------+----------------------+

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.