Output results aggregating column values horizontally

Question

I have this query:

SELECT
count(*), `merchant_id`, `merchant_finance_id`, `merchant_channel_id`, `status`
FROM `application` 
WHERE `created_at` >= '2018-04-30' AND `created_at` < '2018-05-01'
GROUP BY `merchant_id`, `merchant_finance_id`, `merchant_channel_id`, `status`

Which results in the following:

-------------------------------------------------------------------------------
| count(*) | merchant_id | merchant_finance_id | merchant_channel_id | status |
-------------------------------------------------------------------------------
| 2 | D8E2459CD78C | 55E4D520AC1C | 66A1861918C5 | ACCEPTED                   |
| 2 | D8E2459CD78C | 55E4D520AC1C | 66A1861918C5 | REFERRED                   |
| 1 | E50E50212627 | 6F8F15729DA7 | E02ACD64B452 | AWAITING-ACTIVATION        |
| 2 | E50E50212627 | 6F8F15729DA7 | E02ACD64B452 | DEPOSIT-PAID               |
| 1 | E50E50212627 | 6F8F15729DA7 | E02ACD64B452 | READY                      |
| 1 | E50E50212627 | 6F8F15729DA7 | E02ACD64B452 | REFERRED                   |
| 1 | F04FBD7AEB30 | C11CBD2FC1F8 | 21F2D435EA9D | AWAITING-ACTIVATION        |
-------------------------------------------------------------------------------

But is there a way I can arranged the status into sub-columns instead? Eg:

-------------------------------------------------------------------------------
| count(*) | merchant_id | merchant_finance_id | merchant_channel_id | accepted | referred | awaiting_activation | deposit_paid | ready
-------------------------------------------------------------------------------
| 2 | D8E2459CD78C | 55E4D520AC1C | 66A1861918C5 | 2 | 2 | 0 | 0 | 0 |
| 5 | E50E50212627 | 6F8F15729DA7 | E02ACD64B452 | 0 | 1 | 1 | 2 | 1 |
| 1 | F04FBD7AEB30 | C11CBD2FC1F8 | 21F2D435EA9D | 0 | 0 | 1 | 0 | 0 |
-------------------------------------------------------------------------------

Note:

There is a defined list of different status types - so I don't have to worry about "BLAHBLAH" somehow being a status type.

You might want to check out the pivot function, or throw the data into Excel and use a pivot table there. Have a look at this question for more information: [link](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — DavidP, Aug 06 '18 at 17:46

D-Shih · Accepted Answer · 2018-08-06T17:54:15.820

You can try to use condition aggregate function, If you use Mysql you can do simplest like this.

SELECT
    count(*), 
    `merchant_id`,
    `merchant_finance_id`, 
    `merchant_channel_id`, 
    SUM(`status`='ACCEPTED'),
    SUM(`status`='REFERRED'),
    SUM(`status`='awaiting_activation'),
    SUM(`status`='deposit_paid'),
    SUM(`status`='READY')
FROM `application` 
WHERE `created_at` >= '2018-04-30' AND `created_at` < '2018-05-01'
GROUP BY `merchant_id`, `merchant_finance_id`, `merchant_channel_id`

TestDLL

CREATE TABLE T(
   merchant_id varchar(50),
   merchant_finance_id varchar(50),
   merchant_channel_id varchar(50),
   `status` varchar(50)
);



INSERT INTO T VALUES ('D8E2459CD78C', '55E4D520AC1C','66A1861918C5' ,'ACCEPTED');           
INSERT INTO T VALUES ('D8E2459CD78C', '55E4D520AC1C','66A1861918C5' ,'REFERRED');           
INSERT INTO T VALUES ('E50E50212627', '6F8F15729DA7','E02ACD64B452' ,'AWAITING-ACTIVATION');
INSERT INTO T VALUES ('E50E50212627', '6F8F15729DA7','E02ACD64B452' ,'DEPOSIT-PAID');       
INSERT INTO T VALUES ('E50E50212627', '6F8F15729DA7','E02ACD64B452' ,'READY');             
INSERT INTO T VALUES ('E50E50212627', '6F8F15729DA7','E02ACD64B452' ,'REFERRED');           
INSERT INTO T VALUES ('F04FBD7AEB30', 'C11CBD2FC1F8','21F2D435EA9D' ,'AWAITING-ACTIVATION');

Query 1:

SELECT
    `merchant_id`,
    `merchant_finance_id`, 
    `merchant_channel_id`, 
    SUM(`status`='accepted') accepted,
    SUM(`status`='referred') referred,
    SUM(`status`='awaiting_activation') awaiting_activation,
    SUM(`status`='deposit_paid') deposit_paid,
    SUM(`status`='ready') ready
FROM T
GROUP BY `merchant_id`, `merchant_finance_id`, `merchant_channel_id`

Results:

|  merchant_id | merchant_finance_id | merchant_channel_id | accepted | referred | awaiting_activation | deposit_paid | ready |
|--------------|---------------------|---------------------|----------|----------|---------------------|--------------|-------|
| D8E2459CD78C |        55E4D520AC1C |        66A1861918C5 |        1 |        1 |                   0 |            0 |     0 |
| E50E50212627 |        6F8F15729DA7 |        E02ACD64B452 |        0 |        1 |                   0 |            0 |     1 |
| F04FBD7AEB30 |        C11CBD2FC1F8 |        21F2D435EA9D |        0 |        0 |                   0 |            0 |     0 |

Oh, I see. Spot on. Thank you. – mikelovelyuk Aug 06 '18 at 17:53 — mikelovelyuk, Aug 06 '18 at 17:53

Output results aggregating column values horizontally

1 Answers1