SQL calculations based on column value

Question

I have a table transactions:

client_id   Amount   payment ref   contract type
1           300      MTV           1
1           300      MTV           1
1           300      MTV           1
1           150      ML            1
2           150      ML            2
2           150      ML            2
2           150      ML            2
2           150      ML            2

If a client is on contract 1, then their total payments should be 3 x MTV(300) and 3 x ML(150). If they are on contract 2, then they pay 6 x ML(150).

I am trying to figure out how to get the total number of payments remaining for each client based on their contract type. For example client_id = 1:

client_id   type   Total Paid   Payments remaining
1           MTV    900          0
1           ML     150          2

I can do the first three columns using this:

SELECT   `client_id`,
         `payment ref` AS `type`,
         SUM(`Amount`) as `Total Paid` 
FROM     transactions
WHERE    client_id = 1
GROUP BY type;

How do I add on the payments remaining column?

CASE WHEN `contract type` = 1 THEN (3 - COUNT(MTV)... ?

How do you know, what contract a client has?

Paul Spiegel
– Paul Spiegel

2017-08-06 16:31:00 +00:00
Commented Aug 6, 2017 at 16:31 — Paul Spiegel
– Paul Spiegel, Commented Aug 6, 2017 at 16:31

WJS · Accepted Answer · 2017-08-06 16:16:24Z

2

You were pretty close, there was an issue with the GROUP BY clause not including client_id and payment ref:

SELECT 
client_id, 
payment ref AS type, 
SUM(Amount) as 'Total Paid',
CASE 
WHEN contract type = 1 THEN (3 - COUNT(*))
WHEN contract type = 2 THEN (6 -  COUNT(*))
END as 'Payments remaining'
FROM transactions 
WHERE client_id = 1 
GROUP BY client_id, 
payment ref;

answered Aug 6, 2017 at 16:16

WJS

7246 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ScaisEdge · Accepted Answer · 2017-08-06 16:16:19Z

1

could use case when and group by

  select 
      client_id
      , payment
      , case 
          when `contract type` = 1 and `payment ref` = 'MTV' then  3*300 
          when `contract type` = 1 and `payment ref` = 'ML' then  3*150
          when  `contract type` = 2 then 3*150 
        end as  to_pay
      , sum(Amount) payed
      , case 
          when `contract type` = 1 and `payment ref` = 'MTV' then  3*300/SUM(Amount) 
          when `contract type` = 1 and `payment ref` = 'ML' then  3*150/sum(Amount)
          when  `contract type` = 2 then 3*150 / sum(Amount)
        end number_of
        end as  `Payments remaining`
  from my_table 
  group by client_id, `contract type`, `payment ref`

answered Aug 6, 2017 at 16:16

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Comments

Eid Morsy · Accepted Answer · 2017-08-07 05:05:22Z

1

Try the below query

    select *
       ,case contract_type 
            when 1 then 3-paidCount  
            when 2 then 6-paidCount  
       End               
    from (
        select client_id  , payment_ref ,  contract_type,sum(Amount)TotalPaid ,count(*) paidCount
        FROM     transactions
        group by client_id  , payment_ref ,  contract_type
      ) t

answered Aug 7, 2017 at 5:05

Eid Morsy

9666 silver badges8 bronze badges

Collectives™ on Stack Overflow

SQL calculations based on column value

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related