CODEWITHSUNDEEP
mysqlsql
Carlos Morales
Carlos Morales

Reputation: 1149

How can optimize a query without repeat the value of the condition?

I'm trying do a optimization with cia_ensure_values without repeat the same condition for each cia_ensure

My tables:

|policies|
  |id|  |client|  |policy_business_unit_id|  |cia_ensure_id|   |state|       
   1      MATT                  1                   1             0
   2      STEVE                 1                   1             0
   3      BILL                  1                   2             0
   4      LARRY                 1                   2             1
   5      MATT                  1                   2             1
   6      STEVE                 2                   2             2
   7      BILL                  2                   2             2
   8      LARRY                 2                   2             1
   9      MATT                  2                   1             1

|policy_business_units|
   |id|   |name|  |comercial_area_id|
     1     LIFE         1 
     2     ROB          1 
     3     CAR          1 

|comercial_areas|
   |id|   |name|
    1      MICROSOFT
    2      APPLE

|cia_ensures|
   |id|   |name|
    1      ORANGE
    2      BT
    3      ATT
    4      MOVISTAR
    5      SPRINT

Here is the information:

http://sqlfiddle.com/#!2/89491/1

I'm trying to create a query when it has values will repeat in my query

SELECT pb.name as unit,
  SUM(CASE WHEN p.state =0 AND ce.id=1 THEN 1 ELSE 0 END ) AS v1, 
  SUM(CASE WHEN p.state =1 AND ce.id=1 THEN 1 ELSE 0 END ) AS a1, 
  SUM(CASE WHEN p.state =2 AND ce.id=1 THEN 1 ELSE 0 END ) AS n1,
  SUM(CASE WHEN p.state =0 AND ce.id=2 THEN 1 ELSE 0 END ) AS v2, 
  SUM(CASE WHEN p.state =1 AND ce.id=2 THEN 1 ELSE 0 END ) AS a2, 
  SUM(CASE WHEN p.state =2 AND ce.id=2 THEN 1 ELSE 0 END ) AS n2,
  SUM(CASE WHEN p.state =0 AND ce.id=3 THEN 1 ELSE 0 END ) AS v3, 
  SUM(CASE WHEN p.state =1 AND ce.id=3 THEN 1 ELSE 0 END ) AS a3, 
  SUM(CASE WHEN p.state =2 AND ce.id=3 THEN 1 ELSE 0 END ) AS n3,
  SUM(CASE WHEN p.state =0 AND ce.id=4 THEN 1 ELSE 0 END ) AS v4, 
  SUM(CASE WHEN p.state =1 AND ce.id=4 THEN 1 ELSE 0 END ) AS a4, 
  SUM(CASE WHEN p.state =2 AND ce.id=4 THEN 1 ELSE 0 END ) AS n4,
  SUM(CASE WHEN p.state =0 AND ce.id=5 THEN 1 ELSE 0 END ) AS v5, 
  SUM(CASE WHEN p.state =1 AND ce.id=5 THEN 1 ELSE 0 END ) AS a5, 
  SUM(CASE WHEN p.state =2 AND ce.id=5 THEN 1 ELSE 0 END ) AS n5 
  FROM cia_ensures ce 
  LEFT JOIN policies p on ce.id = p.cia_ensure_id
  INNER JOIN policy_business_units pb ON pb.id = p.policy_business_unit_id
  INNER JOIN comercial_areas ca ON ca.id = pb.comercial_area_id
  AND ca.id=1  
  Group by p.policy_business_unit_id;

The query is working fine without problems and showing this result:

 UNIT  V1  A1  N1   V2 A2  N2   V3  A3  N3   V4  A4  N4   V5  A5  N5
 LIFE  2   0   0    1   2  0    0   0   0     0   0   0   0    0   0
 ROB   0   1   0    0   1  2    0   0   0     0   0   0   0    0   0

But WHAT HAPPEN IF I HAVE 100 CIA_ENSURE? My query will be too long with more 100 lines. How can I do creating a value @value where i type my cia_ensure and this will repeat like this:

  @cia_ensure_values = 1,3,5

  for ce.id=1,3,5

  /////This code will be repeated for all cias typed 
  SELECT pb.name as unit,
  SUM(CASE WHEN p.state =0 AND ce.id=@cia_ensure_values THEN 1 ELSE 0 END ) AS v, 
  SUM(CASE WHEN p.state =1 AND ce.id=@cia_ensure_values THEN 1 ELSE 0 END ) AS a, 
  SUM(CASE WHEN p.state =2 AND ce.id=@cia_ensure_values THEN 1 ELSE 0 END ) AS n,

  end              

  FROM cia_ensures ce 
  LEFT JOIN policies p on ce.id = p.cia_ensure_id
  INNER JOIN policy_business_units pb ON pb.id = p.policy_business_unit_id
  INNER JOIN comercial_areas ca ON ca.id = pb.comercial_area_id
  AND ca.id=1  
  Group by p.policy_business_unit_id;

Somebody can help me to optimize this query?

All kind of help will be accepted.

Please I will appreciate all kind of help.

Thanks.

Upvotes: 1

Views: 89

Answers (1)

Ron Smith
Ron Smith

Reputation: 3266

You can easily accomplish this with a short prepared statement. Just include the ids you want in the where id in() clause:

select distinct @sql := concat('SELECT pb.name as unit,',group_concat(concat(
 'SUM(CASE WHEN p.state =0 AND ce.id=',id,' THEN 1 ELSE 0 END ) AS v',id,', 
  SUM(CASE WHEN p.state =1 AND ce.id=',id,' THEN 1 ELSE 0 END ) AS a',id,', 
  SUM(CASE WHEN p.state =2 AND ce.id=',id,' THEN 1 ELSE 0 END ) AS n',id)),
' FROM cia_ensures ce 
  LEFT JOIN policies p on ce.id = p.cia_ensure_id
  INNER JOIN policy_business_units pb ON pb.id = p.policy_business_unit_id
  INNER JOIN comercial_areas ca ON ca.id = pb.comercial_area_id
  AND ca.id=1  
  Group by p.policy_business_unit_id;')
from cia_ensures
where id in(1,3,5);

prepare stmt from @sql;
execute stmt;

SQLFIDDLE: http://sqlfiddle.com/#!2/631b56/23

Upvotes: 1

Related Questions