0

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 |
+---------------------+-------------------------+-----------------------+---------------------+----------------------+

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.