CODEWITHSUNDEEP
mysqlsql
Carlos Morales
Carlos Morales

Reputation: 1149

How can I repeat values, count and group?

I'm trying to create a crosstab but I need a kind of query that repeat each policy_business_unit group by cia_ensures

My tables:

|policies|
  |id|  |client|  |policy_business_unit_id|  |cia_ensure_id|   |state|       
   1      MATT                  1                   1             0
   2      STEVE                 2                   1             0
   3      BILL                  3                   2             0
   4      LARRY                 4                   2             1

|policy_business_units|
   |id|   |name|  |comercial_area_id|
     1     LIFE         1 
     2     ROB          1
     3     SECURE       1
     4     ACCIDENT     1

|comercial_areas|
   |id|   |name|
    1      BANK
    2      HOSPITAL

|cia_ensures|
   |id|   |name|
    1      SPRINT
    2      APPLE

Here is the information:

http://sqlfiddle.com/#!2/4750f/2

I'm trying to get states = 0,1,2 anc count if it doesn't exist or show 0

Select  
  pb.name as BUSINESS_UNITS,
  ce.name as CIA,ca.name as COMERCIAL_AREAS,
  if (p.state = 0, count(p.state), 0) as state_0,
  if (p.state = 1, count(p.state), 0) as state_1,
  if (p.state = 2, count(p.state), 0) as state_2
From policies p 
  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
  INNER JOIN cia_ensures ce ON ce.id = p.cia_ensure_id
where ca.id=1
group by pb.id

I'm getting this result:

BUSINESS_UNITS      CIA      COMERCIAL_AREAS    STATE_0   STATE_1   STATUS_2
 LIFE            SPRINT       BANK                  1       0           0
 ROB             SPRINT       BANK                  1       0           0
 SECURE          APPLE        BANK                  1       0           0
 ACCIDENT        APPLE        BANK                  0       1           0

Here is my problem:

How can I do to have this result?

BUSINESS_UNITS      CIA      COMERCIAL_AREAS    STATE_0   STATE_1   STATUS_2
 LIFE            SPRINT       BANK                  1       0           0
 ROB             SPRINT       BANK                  1       0           0
 SECURE          SPRINT       BANK                  0       0           0
 ACCIDENT        SPRINT       BANK                  0       0           0
 LIFE            APPLE        BANK                  0       0           0
 ROB             APPLE        BANK                  0       0           0
 SECURE          APPLE        BANK                  1       0           0
 ACCIDENT        APPLE        BANK                  0       1           0

I tried

SELECT 
   COALESCE(SUM(CASE WHEN p.state = 0 THEN 1 ELSE 0 END),0) AS state_0,
   COALESCE(SUM(CASE WHEN p.state = 1 THEN 1 ELSE 0 END),0) AS state_1,
   COALESCE(SUM(CASE WHEN p.state = 2 THEN 1 ELSE 0 END),0) AS state_2,
   count(*) AS total, p.policy_business_unit_id as units,
   p.cia_ensure_id AS cias, p.state as status
   FROM policies p
   WHERE policy_business_unit_id IN (1)
   AND cia_ensure_id IN (1,2)
   GROUP BY cia_ensure_id

Somebody knows how to do that?

Please I will appreciate all kind of help. Thanks.

Upvotes: 1

Views: 127

Answers (1)

citizen404
citizen404

Reputation: 1515

You have to use subqueries and then apply a LEFT OUTER JOIN to join them together. The IFNULL part at the top ensures that the filled up NULL values in the not existing state fields, resulting from the outer join, are converted to 0.

SET @id = 1;

SELECT sections.BUSINESS_UNITS, sections.CIA, sections.AREAS,
    IFNULL(states.state_0, 0) AS STATE_0,
    IFNULL(states.state_1, 0) AS STATE_1,
    IFNULL(states.state_2, 0) AS STATE_2
    FROM (
        SELECT
            pb.id AS bus_id, pb.name AS BUSINESS_UNITS,
            ce.id AS cia_id, ce.name AS CIA,
            ca.name AS AREAS
            FROM policies p 
            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
            INNER JOIN cia_ensures ce
            WHERE ca.id = @id
    ) AS sections
    LEFT OUTER JOIN (
        SELECT
            pb.id AS bus_id,
            ce.id AS cia_id,
            if (p.state = 0, 1, 0) AS state_0,
            if (p.state = 1, 1, 0) AS state_1,
            if (p.state = 2, 1, 0) AS state_2
            FROM policies p 
            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
            INNER JOIN cia_ensures ce
            ON ce.id = p.cia_ensure_id
            WHERE ca.id = @id
    ) AS states
    ON sections.bus_id = states.bus_id
    AND sections.cia_id = states.cia_id
    ORDER BY sections.cia_id;

DEMO @ SQL Fiddle

Upvotes: 1

Related Questions