CODEWITHSUNDEEP
sqloracle-database
kimi_f109
kimi_f109

Reputation: 53

One SQL query with multiple conditions

I am running an Oracle database and have two tables below.

#account
+----------------------------------+
| acc_id | date       | acc_type   |
+--------+------------+------------+
| 1      | 11-07-2018 | customer   |
| 2      | 01-11-2018 | customer   |
| 3      | 02-09-2018 | employee   |
| 4      | 01-09-2018 | customer   |
+--------+------------+------------+

#credit_request
+-----------------------------------------------------------------+
| credit_id  |   date      | credit_type | acc_id | credit_amount |
+------------+-------------+----------   +--------+
| 1112       | 01-08-2018  | failed      |  1     |    2200       |
| 1214       | 02-12-2018  | success     |  2     |    1500       |
| 1312       | 03-11-2018  | success     |  4     |    8750       | 
| 1468       | 01-12-2018  | failed      |  2     |    3500       |
+------------+-------------+-------------+--------+---------------+

Want to have followings for each customer:

Upvotes: 1

Views: 86

Answers (3)

Himanshu
Himanshu

Reputation: 3970

I guess the below query is easy to understand and implement. Also, to avoid more and more terms in the CASE statements you can just make use of WITH clause and use it in the CASE statements to reduce the query size.

     SELECT a.acc_id,
               c.credit_type,

                  (distinct  c.credit_id),
                   CASE WHEN 
                   c.credit_type='success'
                   THEN max(date)
                   END CASE,
                   CASE WHEN 
                   c.credit_type='failure'
                    THEN sum(credit_amount)
                    END CASE, 

                       (CASE WHEN 
                       c.credit_type='success'
                      THEN count(*)
                      END CASE )/
                    (    CASE WHEN 
                      c.credit_type='failure'
                    THEN count(*)
                    END CASE)

                 from accounts a LEFT JOIN
               credit_request c on 
              a.acc_id=c.acc_id
             where a.acc_type=  'customer' 
             group by  c.credit_type

Upvotes: 0

GMB
GMB

Reputation: 222672

The following query should do the trick.

SELECT
    acc_id,
    MAX(CASE WHEN credit_type = 'success' AND rn = 1 THEN credit_id     END) as last_successfull_credit_id,
    MAX(CASE WHEN credit_type = 'success' AND rn = 1 THEN cdate         END) as last_successfull_credit_date,
    MAX(CASE WHEN credit_type = 'success' AND rn = 1 THEN credit_amount END) as last_successfull_credit_amount,
    SUM(CASE WHEN credit_type = 'failed' THEN credit_amount ELSE 0 END) total_amount_of_failed_credit,
    SUM(CASE WHEN credit_type = 'failed' THEN 1 ELSE 0 END) / COUNT(*) ratio_success_request
FROM (
    SELECT
    a.acc_id,
    a.cdate adate,
    a.acc_type,
    c.credit_id, 
    c.cdate,
    c.credit_type,
    c.credit_amount,
    ROW_NUMBER() OVER(PARTITION BY c.acc_id, c.credit_type ORDER BY c.cdate DESC) rn
    FROM 
        account a
        LEFT JOIN credit_request c ON c.acc_id = a.acc_id       
) x
GROUP BY acc_id
ORDER BY acc_id

The subquery assigns a sequence to each record, within groups of accounts and credit types, using ROW_NUMBR(). The outer query does conditional aggrgation to compute the different computation you asked for.

This Db Fiddle demo with your test data returns :

ACC_ID | LAST_SUCCESSFULL_CREDIT_ID | LAST_SUCCESSFULL_CREDIT_DATE | LAST_SUCCESSFULL_CREDIT_AMOUNT | TOTAL_AMOUNT_OF_FAILED_CREDIT | RATIO_SUCCESS_REQUEST
-----: | -------------------------: | :--------------------------- | -----------------------------: | ----------------------------: | --------------------:
     1 |                       null | null                         |                           null |                          2200 |                     1
     2 |                       1214 | 02-DEC-18                    |                           1500 |                          3500 |                    .5
     3 |                       null | null                         |                           null |                             0 |                     0
     4 |                       1312 | 03-NOV-18                    |                           8750 |                             0 |                     0

This might be what you are looking for... Since you did not show expected results, this might not be 100% accurate, feel free to adapt this.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

Here is one method:

select a.acct_id, acr.num_fails,
       acr.num_successes / nullif(acr.num_fails) as ratio,  -- seems weird.  Why not just the failure rate?
       last_cr.credit_id, last_cr.date, last_cr.credit_amount
from account a left join
     (select acc_id, 
             sum(case when credit_type = 'failed' then 1 else 0 end) as num_fails,
             sum(case when credit_type = 'failed' then credit_amount else 0 end) as num_fails,
             sum(case when credit_type = 'success' then 1 else 0 end) as num_successes
             max(case when credit_type = 'success' then date else 0 end) as max_success_date
     from credit_request
     group by acct_id
    ) acr left join
    credit_request last_cr
    on last_cr.acct_id = acr.acct_id and last_cr.date = acr.date;

Upvotes: 2

Related Questions