MYSQL Sum row to count and limit to top 15

Question

I have this table: extended

+--------+-----+-----+-----+-----+- -+-----+-----+ | Name | T1 | T2 | T1 | T3 | .. | T19 | T20 | +--------+-----+-----+-----+-----+- -+-----+-----+ | john | 5 | 10 | 50 | 10 | .. | 20 | 8 | | bill | 2 | 8 | 11 | 5 | .. | 9 | 55 | | james | 30 | 15 | 12 | 40 | .. | 13 | 10 | | elsie | 28 | 35 | 20 | 32 | .. | 18 | 1 | | .... | .. | .. | .. | .. | .. | .. | .. | +--------+-----+-----+-----+-----+- -+-----+-----+ 

And I want to return this:

 +--------+-------+-----+-----+-----+-----+- -+-----+-----+ | Name | TOTAL | T1 | T2 | T1 | T3 | .. | T19 | T20 | +--------+-------+-----+-----+-----+-----+- -+-----+-----+ | bill | 250 | 2 | 8 | 11 | 5 | .. | 9 | 55 | | john | 230 | 5 | 10 | 50 | 10 | .. | 20 | 8 | | elsie | 158 | 28 | 35 | 20 | 32 | .. | 18 | 1 | | james | 129 | 30 | 15 | 12 | 40 | .. | 13 | 10 | | .... | .... | .. | .. | .. | .. | .. | .. | .. | +--------+-------+-----+-----+-----+-----+----+-----+-----+ 

Order by TOTAL. This amount is the sum of the best of 15 Tx ...

I don’t understand how to do this.

The table comes from a query (CREATE VIEW) from another table with lots of data.

Can you help me?

At this moment I am doing the sum of ALL Tx, but that is not what I want ...

 SELECT `Name`, (T1+T2+ T3+T4+T5+T6+T7+T8+T9+T10+T11+T12+T13+T14+T15+T16+T17+T18+T19+T20) AS TOTAL, T1,T2, T3,T4,T5,T6,T7,T8,T9,T10,T11,T12,T13,T14,T15,T16,T17,T18,T19,T20 FROM `extended` ORDER BY TOTAL DESC 

Answer 1

If you want to remove the lowest value, this is easy:

 select name, (t1 + . . . + t20) - least(t1, . . . , t20) from table; 

Unfortunately, MySQL does not have an nth function, so getting the second smallest and others is quite difficult.

If you have values ​​on separate lines, you can do:

 select name, sum(t) from (select en.*, if(@name = name, @rn := @rn + 1, @rn := 1) as rn, @name := name from extended_norm en cross join (select @name := '', @rn := 0) const order by name, t desc ) en where rn <= 15 group by name; 

With your data structure, you probably need to write a user-defined function to do what you want.

EDIT:

If you need a list t, you can do this in two ways. You can modify the above to include a pivot point (this assumes you have a column called as tnumber to determine what t value is):

 select name, sum(case when rn <= 15 then t end) as Total, max(case when en.tnumber = 1 then t end) as T1, max(case when en.tnumber = 2 then t end) as T2, . . . max(case when en.tnumber = 1 then t end) as T20 from (select en.*, if(@name = name, @rn := @rn + 1, @rn := 1) as rn, @name := name from extended_norm en cross join (select @name := '', @rn := 0) const order by name, t desc ) en group by name; 

Otherwise, execute the above query and attach it to the denormalized table:

 select e.*, tt.total from extended e join (the above query) tt on e.name = tt.name; 

Answer 2

This is inconvenient, but it may also work!

 select name, sum(t) from (select name, t from ( select name, t1 as t from extended union select name, t2 as t from extended union select name, t3 as t from extended union select name, t4 as t from extended union select name, t5 as t from extended union select name, t6 as t from extended union select name, t7 as t from extended union select name, t8 as t from extended union select name, t9 as t from extended union select name, t10 as t from extended union select name, t11 as t from extended union select name, t12 as t from extended union select name, t13 as t from extended union select name, t14 as t from extended union select name, t15 as t from extended union select name, t16 as t from extended union select name, t17 as t from extended union select name, t18 as t from extended union select name, t19 as t from extended union select name, t20 as t from extended ) z where name='bill' order by name, t desc limit 0,15); 

But you need to run a request for each user, replacing 'bill' different name