Home / Questions / 329459
Accepted
Juan Andres solanas
Asked: 2020-02-15 07:33:07 +0800 CST 2020-02-15 07:33:07 +0800 CST

Get the amount of commissions to pay in MySQL

  • 772

how are they? I would need to get the following amounts:

Show for each contract total amount of commissions, amount to pay, amount to paid.

The tables are the following:

Commissions: contract_number (PK-FK of contracts), payment_date, commission_amount Contracts: contract_number (PK), contract_date.

I made the following query:

select cont.nro_contrato, count(*), if(com.fecha_pago is not null, 
  count(com.fecha_pago) , 0) as pagadas,
    if(com.fecha_pago is null, count(*), 0) as  a_pagar
    from contratos as cont inner join comisiones as com
        on cont.nro_contrato = com.nro_contrato
           group by cont.nro_contrato;

The result is as follows:

enter image description here

But if I ask for the commissions, effectively in contract n° 1 for example, there is an empty payment_date, so it would have to count in payable, and not show 0 as it does:

enter image description here

Could someone give me a hand? Thank you very much!

mysql

1 Answers

  1. Best Answer

    What you are looking for you have to solve inside the function count(), not outside, something like this:

    select cont.nro_contrato, 
       count(*), 
       count(com.fecha_pago) , 0) as pagadas,
       count(case when com.fecha_pago is null then 1 else NULL end) as  a_pagar
       from contratos as cont inner join comisiones as com
            on cont.nro_contrato = com.nro_contrato
       group by cont.nro_contrato;

    It's basically a conditional count, the condition is evaluated inside the aggregation function using a case.

    • 3