CODEWITHSUNDEEP
mysql
Akashii
Akashii

Reputation: 2281

SUM record with multiple join wrong

I have 3 table request, plan, level.

Here is table request: 

 id  plan_id response_code client_id
  1  1        200           1          
  2  1        200           1
  3  1        400           1
  4  1        500           1

Here is table plan: 

 id       client_id
  1             1         
  2             1
  3             1
  4             1

Here is table price:

     id  plan_id
      1  1               
      2  2       
      3  3       
      4  4

I want join 3 table and count in table request, how many record with response_code 200, 400, and 500. But I get wrong value. Here is my query

        SELECT 
      requests.id,
      SELECT SUM(case 
      when requests.response_code = 200 then 
      1 else 0
       end) as resquest200,
      SELECT SUM(case 
      when requests.response_code = 500 then 
      1 else 0
       end) as resquest500,
      response_code,
    FROM requests
    JOIN plan
    ON requests.plan_id = plan.id
    JOIN price
    ON plan.id = price.plan_id
    GROUP BY request.id

I tried: 

 SELECT 
 request.id,
 (
SELECT SUM(case 
when request.response_code = 200 then 
1 else 0
end) FROM request) as resquest200,
response_code,
FROM request
JOIN plan
ON requests.plan_id = plan.id
JOIN price
ON plan.id = price.plan_id
GROUP BY request.id

and it sum correctly. How can I optimize my query? Please help me

Upvotes: 0

Views: 61

Answers (1)

MarkSeeSharp
MarkSeeSharp

Reputation: 126

If you're looking to establishing the total number of requests then COUNT() is a better option than SUM()

SELECT COUNT(*), response_code FROM requests
GROUP BY response_code

Why do you want to join onto plan and price? Is there some data that from these tables that you need to return?

Upvotes: 1

Related Questions