CODEWITHSUNDEEP
mysqlsql
Carlos Morales
Carlos Morales

Reputation: 1149

How can sum group values?

I'm sum when the group state= 0,1,2 and sum 0 when it show 0

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             1
   4      LARRY                 4                   2             1
   5      MESSI                 1                   1             1  
   6      DROGBA                1                   1             1   

|policy_business_units|
   |id|   |name|  |comercial_area_id|
     1     LIFE         2 
     2     ROB          1
     3     SECURE       2
     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/935e78/4

I'm trying to sum states = 0,1,2 in each column:

  SELECT 
   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, 
   p.policy_business_unit_id AS UNITS, 
   p.cia_ensure_id AS CIAS

FROM policies p
WHERE  p.policy_business_unit_id IN ( 1 ) 
AND p.cia_ensure_id =1
GROUP BY p.state

This is the result:

  STATE_0   STATE_1       STATE_2     UNITS    CIAS
    1           0          0           1       1
    0           2          0           1       1

How can I do to have this result?

  STATE_0   STATE_1       STATE_2     UNITS    CIAS
    1           2          0           1       1

Please I will appreciate all kind of help. Thanks.

Upvotes: 0

Views: 50

Answers (3)

DerekCate
DerekCate

Reputation: 306

You might look at grouping by the policy_business_unit_id and cia_ensure_id in part because you may want to report not just for the 1 and 1 but all combinations. 

SELECT 
sum(case when p.state = 0 then 1 else 0 end) AS state_0, 
sum(case when p.state = 1 then 1 else 0 end) AS state_1,
sum(case when p.state = 2 then 1 else 0 end) AS state_2, 
p.policy_business_unit_id AS UNITS, 
p.cia_ensure_id AS CIAS
FROM policies p
--WHERE  p.policy_business_unit_id IN ( 1 ) 
--AND p.cia_ensure_id =1
group by p.policy_business_unit_id, p.cia_ensure_id

Upvotes: 0

user2989408
user2989408

Reputation: 3137

If you need to group all values for state = 0 or 1 or 2 together, then your query must be a little different. Try this.

SELECT 
    SUM(CASE WHEN p.state = 0 THEN 1 ELSE 0 END) AS state_0,
    SUM(CASE WHEN p.state = 1 THEN 1 ELSE 0 END) AS state_1,
    SUM(CASE WHEN p.state = 2 THEN 1 ELSE 0 END) AS state_2,
    p.policy_business_unit_id AS UNITS, 
    p.cia_ensure_id AS CIAS 
FROM policies p
WHERE  p.policy_business_unit_id IN ( 1 ) 
    AND p.cia_ensure_id =1
GROUP BY CASE WHEN p.state IN (0,1,2) THEN 1 ELSE 0 END

Here is a working SQL FIDDLE that shows how this works.

Upvotes: 1

echo_Me
echo_Me

Reputation: 37243

replace this

  SELECT 
   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,

by

 SELECT 
   sum(CASE WHEN p.state =0 THEN  1 ELSE 0 end) AS state_0, 
   sum(CASE WHEN p.state =1 THEN  1  ELSE 0  END) AS state_1,
   sum(CASE WHEN p.state =2 THEN  1  ELSE 0  END) AS state_2,

Upvotes: 1

Related Questions