too long

Question

I have sql request:

SELECT Bank_ID, Status, COUNT(Bank_ID) as Number 
FROM int_client_bank 
WHERE status = 30 or status = 50 or status = 35 or status = 37 
GROUP BY Bank_ID, Status;

And see data:

"Bank_ID"   "Status"    "Number"
"1"         "30"        "772"
"1"         "35"        "58"
"1"         "50"        "151"
"2"         "30"        "124"
"2"         "35"        "27"
"2"         "50"        "25"
"3"         "30"        "227"
"3"         "35"        "16"
"3"         "37"        "1"
"3"         "50"        "143"
"4"         "30"        "337"
"4"         "35"        "23"
"4"         "37"        "1"
"4"         "50"        "98"
"5"         "30"        "72"
"5"         "35"        "7"

{
    "data":[
        {"Bank_Id":"1","Status":"30","Number":"772"},
        {"Bank_Id":"1","Status":"35","Number":"58"},
        ...
    ],
    "success":true
}

I need change request to put data like this:

I need 4 statuses - 30, 35, 50, 37

 Bank_ID Status30 Status35 Status50 Status37
     1        772       58       151      0
     2        30        27        0       0

{
"data":[
            {"Bank_Id":"1","Status30":"772","Status35":"58","Status50":"151","Status37":",},
{"Bank_Id":"2","Status30":"124","Status35":"27","Status50":"25","Status37":"0"},
...
       ],
       "success":true
       }

How i can do that json encode or sql request??

Answer 1

You can use a SUM of the result of an IF statement, to count rows where the status is a particular value:-

SELECT Bank_ID, 
    SUM(IF(Bank_ID = 30, 1, 0)) as Status30,
    SUM(IF(Bank_ID = 50, 1, 0)) as Status50,
    SUM(IF(Bank_ID = 35, 1, 0)) as Status35,
    SUM(IF(Bank_ID = 37, 1, 0)) as Status37
FROM int_client_bank 
WHERE status IN (30, 50, 35, 37 )
GROUP BY Bank_ID;