Sql for plot counting

Asked

Viewed 343 times

2

I have a billing chart. The significant fields are as follows:

| vencimento | data_pagamento | valor_pago |

I need to make an appointment to call me back:

  • The number of unpaid arrears;
  • The number of instalments paid;
  • The number of unpaid instalments which have not yet expired;

I’m trying this way:

SELECT 
COUNT(CASE WHEN vencimento < NOW() AND data_pagamento IS NULL THEN 1 ELSE 0 END) as 'VENCIDAS',
COUNT(CASE WHEN data_pagamento IS NOT NULL THEN 1 ELSE 0 END) as 'PAGAS',
COUNT(CASE WHEN vencimento >= NOW() AND data_pagamento IS NULL THEN 1 ELSE 0 END) as 'A VENCER'
FROM boletos WHERE cliente = 12345;

But it does not return me the correct values.
Is there any better way ?
What I’m doing wrong using CASE WHEN ?

mysql

2 answers

1


The COUNT you’re using will always return 1, be it COUNT(1) or COUNT(0) or even COUNT(1000), which is why it is not delivering the expected results.

The solution is to use the SUM, to be added 1 where the condition is met and 0 otherwise:

SELECT      cliente
        ,   SUM(CASE WHEN vencimento < NOW() AND data_pagamento IS NULL     THEN 1 ELSE 0 END)  AS 'VENCIDAS'
        ,   SUM(CASE WHEN data_pagamento IS NOT NULL                        THEN 1 ELSE 0 END)  AS 'PAGAS'
        ,   SUM(CASE WHEN vencimento >= NOW() AND data_pagamento IS NULL    THEN 1 ELSE 0 END)  AS 'A VENCER'
FROM        boletos 
GROUP BY    cliente
ORDER BY    cliente

If you want to filter by customer just add the clause WHERE:

SELECT      cliente
        ,   SUM(CASE WHEN vencimento < NOW() AND data_pagamento IS NULL     THEN 1 ELSE 0 END)  AS 'VENCIDAS'
        ,   SUM(CASE WHEN data_pagamento IS NOT NULL                        THEN 1 ELSE 0 END)  AS 'PAGAS'
        ,   SUM(CASE WHEN vencimento >= NOW() AND data_pagamento IS NULL    THEN 1 ELSE 0 END)  AS 'A VENCER'
FROM        boletos 
WHERE       cliente = 12345
GROUP BY    cliente

  • It is necessary this group by or order by by filtering only by one customer ?

  • The ORDER BY only makes sense if you don’t filter through a customer. The GROUP BY must always exist because of the column cliente who is in the SELECT, but if you remove it from the query you can also remove the GROUP BY.

0

There must be a better way to do it, I would do with CTE on Oracle (which is what I use) but on Mysql I would try something like this:

select (select count('X') from boletos where vencimento < '2019-01-23' AND data_pagamento IS NULL and cliente = 12345),
   (select count('X') from boletos where data_pagamento IS NOT NULL and cliente = 12345),
   (select count('X') from boletos where vencimento >= '2019-01-23' AND data_pagamento IS NULL and cliente = 12345) from dual

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.