Looking for JOIN or nested query by which I can get the list of Providers data as mentioned below needs

Question

Table: Users

|_id|name    |
|---|--------|
|115|Abhalo  |
|106|vesiy   |

Note:- Here _id is the Primary key of users table.

Table: Rides

|_id|total_fair|provider_id|is_admin_paid|
|---|----------|-----------|-------------|
|267|     480.0|        115|            0|
|370|      60.0|        106|            1|
|258|     200.0|        115|            1|

Note:- Here provider_id is the foreign key from users table.

Output:- Here I have to use the is_admin_paid flag for getting the total paid_amount and total unpaid_amount.

Need Output:-

|provider_id|driver_name|total_rides|paid_amount|unpaid_Amount|
|-----------|-----------|-----------|-----------|-------------|
|        115|     Abhalo|          2|        200|          480|
|        106|      Vesiy|          1|         60|            0|

I'm beginner in PostqreSQL. And facing this situation for listing data. Now don't want to use loop for paid_amount and unpaid_amount.

Thank you in advance. I really appreciate your time for looking my question.

Answer 1

SELECT R.provider_id, 
       U.name as driver_name,
       count(*) as total_rides,
       sum(case when R.is_admin_paid = 1 then total_fair else 0 end) as paid_amount,
       sum(case when R.is_admin_paid = 0 then total_fair else 0 end) as unpaid_amount
FROM users U JOIN Rides R 
ON R.provider_id = U._id
GROUP BY R.provider_id , U.name
ORDER BY count(*) DESC

Here is example for it : Example