MYSQL / SQL interest rate queries percentage
What do I have
$query =
"SELECT
status_type,
COUNT(*) as count,
kpi_type,
COUNT('kpi') * 100.00 as percentage
FROM
main
GROUP BY
status_type DESC;";
What do I get.
[{"Status":"SOLD","KPI_Percentage":"1400","KPI":"SALE","Status_Count":"14"}]
Here is my JSON array
$JSON_output[] = array('Status' => $row['pin_status'],
'KPI' => $row['kpi_type'],
'KPI_Percentage' => $row['percent'],
'Status_Count' => $row['count'],);}}
What I am trying to do ...
[{"status":"value","kpi":"value","count":"12""percentage":"%32.42"}]
I try to count status_type
and show status_type
and I try to show kpi_type
and count% of kpi_type
compared to others kpi_type
, so if I have 3 sold and 2 leadership and 5 prospects: "LEADER: 20% SOLD 30% PROSPECTS 50%"
Table
+------+-----------------+------------------+--------------+
| ID | kpi_type | status_type | Is Deleted |
+------+-----------------+------------------+--------------+
| 1 | SALE |SOLD | no |
| 2 | LEAD |Maybe | no |
| 3 | LEAD |Hot Lead | no |
| 4 | PROSPECT |Not Home | no |
| 5 | SALE |SOLD | no |
| 6 | LEAD |Maybe | no |
| 7 | LEAD |Hot Lead | no |
| 8 | Not Interested |Not Interested | no |
+------+-----------------+------------------+--------------+
What answer am I looking for ...
$query = //// THE CORRECT GOODS TO INSERT INTO THIS QUERY ////
"SELECT
status_type,
COUNT(*) as count,
kpi_type,
COUNT('kpi') * 100.00 as percentage
FROM
main
GROUP BY
status_type DESC;";
Let me know if you have any questions.
source to share
I don't have MySql in front of me right now, but you need to get the total of all records and then group by status and KPI something like this (this is a crude syntax - it might bring you closer):
select @Total:=Count(*) from InputTable;
select `Status`, `Kpi`, Count(*) as Status_Count, ((Count(*) * 100.0)/ @Total) AS KPI_Percentage
from InputTable
group by `Status`, `Kpi`
order by `Status`, `KPI`;
Query results
Invalid query: You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'select [status_type], [kpi_type], Count(), ((Count() * 100.0)/ @Total)
AS Perc' at line 3 Whole query: select @Total =(select Count() from main_pins)
select [status_type], [kpi_type], Count(), ((Count(*) * 100.0)/ @Total)
AS Percentage from main_pins group by [status_type], [kpi_type] order by [status_type],
[kpi_type]
source to share
Was able to get an answer after fiddling with Ravenyanigam's answer. Thanks everyone for helping and helping to get attention.
$query = "SELECT status_type, kpi_type, Count(*) as count,((Count(*) * 100.0)/ (select Count(*) FROM main)) AS Percentage
FROM main
GROUP BY status_type, kpi_type
ORDER BY status_type, kpi_type";
the given result.
[{"status_type":"Hot Lead","KPI":"Lead","Percentage":"0.31596","Count":"2"}
source to share