CODEWITHSUNDEEP
phpmysql
Dariusz Rup
Dariusz Rup

Reputation: 29

Grouping query results in sql

For example, I have a column: bank_id and status.

Using query: 

SELECT Bank_ID, status FROM int_client_bank WHERE status = 30 or status = 50 or status = 35 or status = 37;

I get the result as:

for example.

**id_bank status**
    1, 30
    1, 30
    1, 50
    1, 35
    2, 50
    2, 37

etc ..

I need to make a query that for each bank can count me if there was a percentage of status and later using php / symfony throw it to the table.

ect: 1 bank: -

status30 - 2 each (20%)
 status50 - 4 each (40%)
 status - 6 each (60%)

2 bank ect ..

How to do it?

Upvotes: 0

Views: 57

Answers (3)

Dariusz Rup
Dariusz Rup

Reputation: 29

sphirate.thremer:

I have request:

SELECT Bank_ID, Status, COUNT(Bank_ID) 
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"    "COUNT(Bank_ID)"
"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"
"5" "50"    "9"
"6" "30"    "113"
"6" "35"    "3"
"6" "50"    "68"
"7" "30"    "16"
"7" "50"    "10"
"8" "30"    "13"
"8" "35"    "1"
"8" "50"    "6"
"9" "30"    "16"
"9" "35"    "2"
"9" "50"    "6"
"10"    "30"    "4"
"10"    "35"    "2"
"11"    "30"    "2"
"11"    "50"    "2"
"12"    "30"    "4"
"12"    "35"    "1"
"12"    "50"    "1"
"13"    "30"    "3"
"13"    "50"    "2"
"14"    "30"    "5"
"15"    "30"    "1"
"15"    "50"    "1"
"16"    "30"    "1"
"17"    "30"    "1"
"18"    "30"    "2"

How i can put this in symfony to make JsonResponse?:

return new JsonResponse(array('data' => $result, 'success' => true));:

Upvotes: 0

duffn
duffn

Reputation: 3760

I believe this is what you're looking for. You can do it all in MySQL with a subquery.

select 
  myTable.id_bank, myTable.status, count(*) as ct, count(*) / t2.cnt * 100 as pct from myTable
join (
    select id_bank, count(*) AS cnt 
    from myTable
    group by id_bank
  ) as t2 on myTable.id_bank = t2.id_bank
group by myTable.id_bank, myTable.status;

http://sqlfiddle.com/#!2/46834/11

Upvotes: 0

sphirate.thremer
sphirate.thremer

Reputation: 104

Use grouping in your SQL to group by bank and status and count rows from each group:

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

Then you can sum all count and for each row calculate percentage like $row['count'] / $all_count * 100.

Upvotes: 1

Related Questions