CODEWITHSUNDEEP
mysqlselect
Manny Calavera
Manny Calavera

Reputation: 6873

MySQL pull data from same table, SUM multiple columns with conditions

I have created a users table that holds names and phone numbers (users).

id| name  | phone

1 | Frank | 0345221234
2 | Sara  | 0342555939

I got another table that holds a log with user's calls to different numbers (call_logs):

number     | destination | price

0345221234 | destination | x             /// This is Frank
0345221234 | destination | y             /// This is also Frank
0342555939 | destination | z             /// This is Sara

And then I have a table that holds numbers that Frank and Sara are allowed to call (allowed_numbers):

number

033485733
045727728
082358288

I would like to loop through my users table and based on their number to check the calls log table and select the SUM of price column for log records where destination does not match the allowed numbers table so that I know the cost for calls not in the allowed list.

Then I want to select SUM of price column for log records where destination DO match the allowed numbers table so that I know how much did the allowed calls cost.

Is there any way I can do this in a single query with sub-queries and all needed in order to achieve this result set:

users number | SUM(price) of allowed calls | SUM(price) of calls not allowed

Thank you!

Upvotes: 0

Views: 240

Answers (1)

AgRizzo
AgRizzo

Reputation: 5271

SELECT call_logs.number
  ,SUM(IF(allowed_numbers.number IS NOT NULL,call_logs.price,0)) AS AllowedPrice
  ,SUM(IF(allowed_numbers.number IS NULL,call_logs.price,0)) AS NotAllowedPrice
FROM call_logs
LEFT JOIN allowed_numbers
  ON call_logs.destination = allowed_numbers.number
GROUP BY call_logs.number;

Upvotes: 2

Related Questions