SUM,COUNT with UNION in MySql

Question

I have a table that has the following schema:

DATA | CAUSE_1 | TIME_1 | CAUSE_2 | TIME_2 | CAUSE_3 | TIME_3

The CAUSE.* field (VarChar) can not contain any string, and if so, the field TIME.* is 0.

I'm trying to create a query, but unfortunately without success, where I would have the result display in this form:

CAUSE | TOT_TIME | N_RIPET_CAUSE,

where:

• In CAUSE I have a list of what is contained in CAUSE_1 ... CAUSE_3,
• In TOT_TIME the sum of the values in TIME_1 ... TIME_3,
• In N_RIPET_CAUSE the number of repetitions of each CAUSE.

I hope I explained.

Answer 1

try this

 SELECT DATA ,CAUSE , TOT_TIME , N_RIPET_CAUSE
 FROM ( select DATA, CONCAT(`CAUSE_1`,' ',`CAUSE_2`, ' ', `CAUSE_3`) as CAUSE ,
 sum(`TIME_1` + `TIME_2` +`TIME_3`) as TOT_TIME ,
 (count(`CAUSE_1`)+count(`CAUSE_2`)+count(`CAUSE_3`)) as N_RIPET_CAUSE
 FROM your_table
 group by DATA
 ) t

SEE SQLFIDDLE DEMO

EDIT.

try this

     ( select DATA , `CAUSE_1` as CAUSE ,
     sum(`TIME_1` + `TIME_2` +`TIME_3`) as TOT_TIME ,
     (count(`CAUSE_1`)+count(`CAUSE_2`)+count(`CAUSE_3`)) as N_RIPET_CAUSE
     FROM Table1 
    group by DATA)
  union all
    (select DATA , `CAUSE_2` as CAUSE ,
    sum(`TIME_1` + `TIME_2` +`TIME_3`) as TOT_TIME ,
    (count(`CAUSE_1`)+count(`CAUSE_2`)+count(`CAUSE_3`)) as N_RIPET_CAUSE
    FROM Table1
    group by DATA   )
  union all

    (select DATA , `CAUSE_3` as CAUSE ,
    sum(`TIME_1` + `TIME_2` +`TIME_3`) as TOT_TIME ,
    (count(`CAUSE_1`)+count(`CAUSE_2`)+count(`CAUSE_3`)) as N_RIPET_CAUSE
    FROM Table1
    group by DATA   )

SQL DEMO HERE

EDIT:

try this due to your need

 select cause, sum(time) Tot_time, count(cause) N_Ripet_Cause
 from(
    select  cause_1 as cause, time_1 as time
    from Table1
    union all
    select  cause_2 as cause, time_2 as time
   from Table1
   union all
   select  cause_3 as cause, time_3 as time
   from Table1
 ) t
 group by cause

DEMO SQL FIDDLE

Answer 2

If you cannot change the table structure, then in order to get this result, you are going to need to unpivot the columns into rows.

MySQL does not have an unpivot function but this can be done using a UNION ALL query. You can then apply the aggregate to those values to get the final result:

select cause, sum(time) Tot_time, count(cause) N_Ripet_Cause
from
(
  select data, cause_1 as cause, time_1 as time
  from yourtable
  union all
  select data, cause_2 as cause, time_2 as time
  from yourtable
  union all
  select data, cause_3 as cause, time_3 as time
  from yourtable
) src
group by cause