How to solve this exercise in MySQL

Question

How to solve this exercise in MySQL

Navigation

#1 by (2 votes)

2

Can anyone help me with this exercise ?:

Show for each contract the total amount of commissions, amount payable, amount paid.

The tables that are named in it are:

Contracts: dni, cuit, nro_contract, cod_cargo, salary, percentage_comision, date_incorporation, date_finalization_contract, expiration date, application_date

Commissions: anio_contrato, month_contract, nro_contract, import_commission, payment_date

And what I try to do partially is:

select nro_contrato as 'NUMERO CONTRATO', 
count(*) as 'CANTIDAD TOTAL DE COMISIONES', *************
from comisiones 
group by 1;

But I can not understand how to make part of the paid and to pay.

Pd: The ************* is where I can not think of what to put.

If it has already been paid, it has a date and if it is not going to be invalid the value of payment_date
The amount to pay implies the number of commissions that still have to be paid (that is, if there are 4 commissions that have to be paid within a month, it means that there are 4 'amounts to be paid') and the amounts paid are the number of commissions that have already been paid (if there are 3 commissions that were paid yesterday, it means that there are 3 'amounts paid')

mysql sql

asked by Damian Ricobelli 14.12.2016 в 20:36

source

1 answer

What does int mapped int32 or int32 mapped int mean? Pass a variable to the controller from AJAX in CodeIgniter

score 2 · Accepted Answer

You can use a condition within the COUNT function to distinguish commissions paid (with date) and unpaid (without date). In the case of unpaid, you can use a CASE expression to filter the records that have fecha_pago to null . For payments, you only need to do count(fecha_pago) , which automatically only counts the records where fecha_pago is not null .

select nro_contrato,
       count(*) as total,
       count(case when fecha_pago is null then 'X' end) as no_pagadas,
       count(fecha_pago) as pagadas
  from comisiones
 group by nro_contrato

Another option that may seem a bit simpler according to your taste is to determine the unpaid ones by subtracting the payments from the total amount:

select nro_contrato,
       count(*) as total,
       count(*) - count(fecha_pago) as no_pagadas,
       count(fecha_pago) as pagadas
  from comisiones
 group by nro_contrato