如何使SQL查询获取特定列的总和? [英] How to make SQL Query to get the sum of specific column?
问题描述
以前,我使用Ajax绘制了一个Google图表(饼图),该图表可以显示,因为它仅涉及2列.
Previously I draw a google chart (Pie Chart), using Ajax and the chart can be displayed as it only involved 2 columns.
现在,我想绘制一个像这样的柱状图.
Now, I want to draw a column chart like this one.
这是我的SQL查询:
$sql = "Select count(application_id) as count, application_status, category_group from mdl_local_jobs_application
inner join mdl_local_jobs_job on mdl_local_jobs_application.application_jobid = mdl_local_jobs_job.job_id
left join mdl_local_jobs_category on mdl_local_jobs_job.job_categoryid = mdl_local_jobs_category.category_id
left join mdl_cohort_members on mdl_local_jobs_application.application_applicantid = mdl_cohort_members.userid
left join mdl_local_cohortrole on mdl_cohort_members.cohortid = mdl_local_cohortrole.cohortid
left join mdl_role on mdl_local_cohortrole.roleid = mdl_role.id where shortname = 'graduates'
group by application_status, category_group";
使用以下代码将查询编码到Json输出中:
The query is coded into Json output using this:
$result_rows = mysql_num_rows($query);
$row_num = 0;
//loop fetching the query into json format
while ($result = mysql_fetch_array($query))
{
$row_num++;
if ($row_num == $result_rows){
echo "{\"c\":[{\"v\":\"" . $result['category_group'] . "\",\"f\":null},{\"v\":\"" . $result['application_status'] . "\",\"f\":null},{\"v\":" . $result['count'] . ",\"f\":null}]},";
} else {
echo "{\"c\":[{\"v\":\"" . $result['category_group'] . "\",\"f\":null},{\"v\":\"" . $result['application_status'] . "\",\"f\":null},{\"v\":" . $result['count'] . ",\"f\":null}]},";
}
}
echo " ] }";
这是输出:
{ "cols": [ {"id":"","label":"CATEOGRY","pattern":"","type":"string"}, {"id":"","label":"CATEOGRY","pattern":"","type":"number"}, {"id":"","label":"TOTAL","pattern":"","type":"number"} ], "rows": [ {"c":[{"v":"Accounting/Finance","f":null},{"v":"0","f":null},{"v":2,"f":null}]},{"c":[{"v":"Engineering","f":null},{"v":"0","f":null},{"v":1,"f":null}]}, ] }
但是,由于应用程序状态的值不是数字,因此图表无法正确显示.我确实阅读了其他解决方案,以通过sql中的sum函数获得结果,但是我不知道该怎么做.
However, the chart don't display correctly because the value of application status is not a number. I do read on the other solutions to get the result by sum function in sql but I do not know how to do it.
我的问题:我想如何执行SQL查询以获取每个应用程序状态(0,1,2--状态类型)的总数
My question: How I want to do SQL Query to get the total count of each application status (0,1,2--status type)
我的预期输出是这样的:
My expected output is to be like this:
{ "cols": [ {"id":"","label":"CATEOGRY","pattern":"","type":"string"}, {"id":"","label":"CATEOGRY","pattern":"","type":"number"}, {"id":"","label":"TOTAL","pattern":"","type":"number"} ], "rows": [ {"c":[{"v":"Accounting/Finance","f":null},{"v":4,"f":null},{"v":2,"f":null}]},{"c":[{"v":"Engineering","f":null},{"v":7,"f":null},{"v":1,"f":null}]}, ] }
区别在于第二个数组值,我想通过执行SQL查询(使用sum函数)来获取该值,以获取状态为0状态,1状态和2状态的每个状态的计算总和.
The difference is in the second array value, I want to have the value by making SQL Query (using sum function) to get calculated sum for each status that are 0 status, 1 status, and 2 status.
{"c":[{"v":"Accounting/Finance","f":null},{"v":4,"f":null},{"v":2,"f":null}]},
如果我执行此查询(更改where语句,则只能获取状态"0"的计数.如何在同一查询中添加另一个where语句以获取其他状态的计数,即状态"1""和状态"2"?
If I make this Query(changes in where statement, I can only get the count of status "0" only. How I want to add another where statement in the same query to get the count of other status, status "1" and status "2"?
$sql = "Select count(application_id) as count, category_group from mdl_local_jobs_application
inner join mdl_local_jobs_job on mdl_local_jobs_application.application_jobid = mdl_local_jobs_job.job_id
left join mdl_local_jobs_category on mdl_local_jobs_job.job_categoryid = mdl_local_jobs_category.category_id
left join mdl_cohort_members on mdl_local_jobs_application.application_applicantid = mdl_cohort_members.userid
left join mdl_local_cohortrole on mdl_cohort_members.cohortid = mdl_local_cohortrole.cohortid
left join mdl_role on mdl_local_cohortrole.roleid = mdl_role.id where shortname = 'graduates' and application_status = 0
group by category_group";
推荐答案
我尝试了以下SQL查询:
I tried this SQL Query:
$sql = "Select count(application_id) as count, sum(case when application_status = 0 then 1 else 0 end) PendingCount, sum(case when application_status = 1 then 1 else 0 end) RejectedCount, sum(case when application_status = 2 then 1 else 0 end)SuccessCount, category_group from mdl_local_jobs_application
inner join mdl_local_jobs_job on mdl_local_jobs_application.application_jobid = mdl_local_jobs_job.job_id
left join mdl_local_jobs_category on mdl_local_jobs_job.job_categoryid = mdl_local_jobs_category.category_id
left join mdl_cohort_members on mdl_local_jobs_application.application_applicantid = mdl_cohort_members.userid
left join mdl_local_cohortrole on mdl_cohort_members.cohortid = mdl_local_cohortrole.cohortid
left join mdl_role on mdl_local_cohortrole.roleid = mdl_role.id where shortname = 'graduates'
group by category_group";
这是输出:
{ "cols": [ {"id":"","label":"CATEOGRY","pattern":"","type":"string"}, {"id":"","label":"PENDING","pattern":"","type":"number"}, {"id":"","label":"REJECTED","pattern":"","type":"number"}, {"id":"","label":"SUCCESS","pattern":"","type":"number"}, {"id":"","label":"TOTAL","pattern":"","type":"number"} ], "rows": [ {"c":[{"v":"Accounting/Finance","f":null},{"v":2,"f":null},{"v":0,"f":null}, {"v":0,"f":null},{"v":2,"f":null}]},{"c":[{"v":"Engineering","f":null},{"v":1,"f":null},{"v":0,"f":null}, {"v":0,"f":null},{"v":1,"f":null}]}, ] }
可以根据状态类型显示图表.
The chart can appear according to the status type..
无论如何,谢谢@ Alex,@ Oleg的评论和分享,以帮助我摆脱困境.很抱歉让您对我的问题感到困惑.
Anyway, thank you @Alex, @Oleg for your comment and sharing to get me out from the problem. I'm sorry for getting you into puzzle with my problem.
这篇关于如何使SQL查询获取特定列的总和?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!