mysql - 如何获取具有分组条件的行数
问题描述
我想获取数量 > 0的行数
没有计数功能我的查询
SELECT pd.student_admission_id,
sum(fcm.fee_amount)- coalesce((SELECT sum(ft.amount_paid) FROM fee_transactions ft where ft.student_id=pd.student_admission_id GROUP BY ft.student_id), 0) as due_amount
FROM fee_class_mapping fcm INNER JOIN student_present_class_details pd ON pd.class_id = fcm.class_id GROUP BY pd.student_admission_id;
结果
+----------------------+------------+
| student_admission_id | due_amount |
+----------------------+------------+
| 1 | 0 |
| 2 | 12000 |
| 3 | 12000 |
+----------------------+------------+
我想计算谁的到期金额> 0
如何用上述查询编写计数函数?
谁能帮我吗 ?。谢谢你!。
解决方案
对于您的第一个查询,您可以使用以下连接重写它
SELECT pd.student_admission_id,
SUM(fcm.fee_amount) - COALESCE(ft.amount_paid, 0) AS due_amount
FROM student_present_class_details pd
INNER JOIN fee_class_mapping fcm ON pd.class_id = fcm.class_id
LEFT JOIN(
SELECT student_id,SUM(ft.amount_paid) amount_paid
FROM fee_transactions
GROUP BY student_id
) ft ON ft.student_id=pd.student_admission_id
GROUP BY pd.student_admission_id
要获得 due_amount > 0 的计数,您可以将上述查询包装为子查询
SELECT COUNT(*)
FROM (
SELECT pd.student_admission_id,
SUM(fcm.fee_amount) - COALESCE(ft.amount_paid, 0) AS due_amount
FROM student_present_class_details pd
INNER JOIN fee_class_mapping fcm ON pd.class_id = fcm.class_id
LEFT JOIN(
SELECT student_id,SUM(ft.amount_paid) amount_paid
FROM fee_transactions
GROUP BY student_id
) ft ON ft.student_id=pd.student_admission_id
GROUP BY pd.student_admission_id
) t
WHERE t.due_amount > 0
推荐阅读
- r - 将环境中的所有 ggplot2 图存储在列表中
- r - 如果 Date 为 max(date),则创建具有 diff 和 Principal 总和的新列
- react-native - BottomTabNavigator 内容出现两次
- html - 仅使用 css 将标签移动到复选框的左侧
- c# - 切换到另一条路径时,Blazor 断开连接
- php - 如何使用 hasMany 迭代和添加“随机”键/值到现有对象
- c# - 如何使用 Verify() 方法仅验证某些属性?
- java - java.sql.SQLException: ResultSet 只能以正向访问
- selenium - 无法切换到框架(看起来也像模态窗口),也尝试使用完整的 xpath
- google-cloud-platform - 将 BigQuery 作为源连接到另一个 GCP 项目中的数据融合