CODEWITHSUNDEEP
mysqldatabase
mark
mark

Reputation: 1295

performing Groupby in large data set MySQL

I have data like this in my table in success column 1 -> success 0 -> failed

MariaDB [mark_db]> select * from Ntv_statistics;

Gives :

| DateTime            | Address      | Msisdn       | ReqType| Success      |
|---------------------|--------------|--------------|--------|--------------|
| 2020-12-21 18:20:32 | [email protected]| 919876543211 | Reg    |      1       |
| 2020-12-21 18:21:34 | [email protected] | 919876543211 | DReg   |      1       |
| 2020-12-21 18:22:16 | [email protected] | 919876543213 | Reg    |      0       |
| 2020-12-21 18:24:27 | [email protected] | 919876543214 | Reg    |      1       |
| 2020-12-21 18:26:34 | [email protected]| 919876543211 | Reg    |      0       |

After filtering I want data something like this:

Msisdn ReqType Success(Count) failure(count) total(count) Success(%) Failure(%)
919876543211 Reg 1 1 2 50 50
919876543211 DReg 1 0 1 100 0
919876543213 Reg 0 1 1 0 100
919876543214 Reg 1 0 1 100 0

How to get this data from the first table

I tried query like below but that's not proper

select Msisdn,ReqType,count(Success) as success,count(Success) as failure,count(*) as total 
from Ntv_statistics 
group by Msisdn,ReqType;

Upvotes: 0

Views: 32

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521854

Using conditional aggregation with boolean expressions we can try:

SELECT
    Msisdn,
    ReqType,
    SUM(Success = 1) AS Success_Count,
    SUM(Success = 0) AS Failure_Count,
    100.0 * SUM(Success = 1) / COUNT(*) AS Success_Pct,
    100.0 * SUM(Success = 0) / COUNT(*) AS Failure_Pct
FROM Ntv_statistics
GROUP BY
    Msisdn,
    ReqType;

Upvotes: 2

Related Questions