Reputation: 16367
I have a table like this , Now I need to get all data GROUP BY tpi
+---------------------+---------+----------------+
| trxDate | trxType | tpi |
+---------------------+---------+----------------+
| 2018-06-28 00:00:00 | Sent | SI0005 |
| 2018-07-02 00:00:00 | Sent | SI0005 |
| 2018-07-04 00:00:00 | Sent | SI0005 |
| 2018-05-25 00:00:00 | Open | SI0007 |
| 2018-06-26 00:00:00 | Open | SI0007 |
| 2018-05-25 00:00:00 | Sent | SI0007 |
| 2018-06-23 00:00:00 | Sent | SI0007 |
+---------------------+---------+----------------+
I need records to sum open and sent
+---------------------+---------+----------------+
| tpi | open | sent |
+---------------------+---------+----------------+
| SI0005 | 0 | 3 |
| SI0007 | 2 | 2 |
+---------------------+---------+----------------+
I have tried with some subquery but not getting desired response
SELECT tblcount.tpi, tblcount.qType, count(tblcount.id) total FROM (SELECT id, tpi, 'Open' qType FROM `tbl`WHERE trxType = 'Open' UNION SELECT id, tpi, 'Sent' qType FROM `tbl` WHERE trxType = 'Sent') tblcount GROUP BY tblcount.third_party_id, tblcount.qType
Upvotes: 0
Views: 57
Reputation: 1271231
Just use conditional aggregation:
select tpi,
sum( (trx_type = 'open') ) as num_open,
sum( (trx_type = 'sent') ) as num_sent
from tbl
group by tpi;
This uses a MySQL extension that treats a boolean expression as an integer in an integer context, with "1" for true and "0" for false.
Upvotes: 1
Reputation: 50173
You can do conditional aggregation :
select tpi,
sum(trxType = 'Open') as trxType_open,
sum(trxType = 'Sent') as trxType_Sent
from tbl t
group by tpi;
Upvotes: 1