如何获取条件为groupby的行数

qfe3c7zg 于 2021-06-20 发布在 Mysql

关注(0)|答案(1)|浏览(327)

我想得到数量大于0的行数
没有out count函数我的查询

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
如何用上述查询编写计数函数？
有人能帮我吗？。谢谢您！。

mysql count

来源：https://stackoverflow.com/questions/51149121/how-to-get-count-of-rows-with-condition-having-group-by

1条答案

按热度按时间

6vl6ewon1#

对于您的第一个查询，您可以使用如下连接重写它

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

要获取到期金额大于0的计数，可以将上述查询 Package 为子查询

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

赞(0）回复(0）举报 2021-06-20

我来回答

如何获取条件为groupby的行数

1条答案

相关问题

热门标签

最新问答