Clever Geek Handbook

SUM, COUNT with UNION in MySql

I have a table that has the following schema:

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

Field CAUSE.*

( VarChar

) cannot contain any string, and if so, then field TIME.*

is 0.

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

CAUSE | TOT_TIME | N_RIPET_CAUSE,

Where:

  • Q CAUSE

    I have a list of what is contained in CAUSE_1 ... CAUSE_3

    ,
  • In the TOT_TIME

    sum of the values ​​in TIME_1 ... TIME_3

    ,
  • In the N_RIPET_CAUSE

    number of repetitions of each CAUSE

    .

I hope I have explained.

+3


source to share


4 answers


try it

 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

CM. SQLFIDDLE DEMO

EDIT.

try it

     ( 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 because of your needs

 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

+2


source


You can choose from union like this:

select * from
(
    select cause_1 as cause, time_1 as time from tableName
    union
    select cause_2 as cause, time_2 as time from tableName
    union
    select cause_3 as cause, time_3 as time from tableName
) as joinedValues


Then you can perform any action from that selection. As well as the amount of each sentence:

select cause, count(cause) from
(
...
) as joinedValues
group by cause
+2


source


If you cannot change the structure of the table, then you will need to expand the columns into rows to get this result.

MySQL does not have a univot function, but it can be done using a query UNION ALL

. Then you can aggregate these 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
+1


source


Jack is at the mark - you have too many redundant cells in your table structure. Use relationships to fix these cases.

DataTable dID | Data

instancesTable ID | dID | REASON | TIME

Then use a NATURAL JOIN on two tables to extract the information;

SELECT * FROM DataTable NATURAL JOIN instancesTable WHERE dID=? LIMIT 3

This query will return a list of reasons and times for all events that occurred in the Data identifier in the first table.

Edit: * N_RIPET_CAUSE * can be found using SUM ( REASON ) on dID.

0


source






More articles:


All Articles