2

I have a table named POIN and has a column which have comma separated values. I want to calculate each values on the comma separated. It's looks duplicate question because it has answered here. But I want to achieved this using single query instead of create a mysql function.

This is my table looks like :

id    poin
------------------
1     1,5,9,3,5
2     2,4,8,5
3     4,7,9,1,5,7

Desired result :

id     max     min      sum      avg
--------------------------------------
1      1       9        23         4,6
2      8       2        19        4,75
3      9       1        33        5,5

Actually, I searched this in Google and this forum and didn't get a correct answer yet. I can't show what I have tried so far, because I have no clue where to start.

Community
  • 1
  • 1
Zacurned Ace
  • 95
  • 2
  • 11
  • Can you explain why a function won't work for you? – Nathan Tuggy Jun 10 '15 at 03:49
  • 3
    This schema is wrong and broken. You really want a table with a column for id and column for poin, where the same id is repeated over and over, but each row only has one entry in the poin field for that id. – Joel Coehoorn Jun 10 '15 at 04:13
  • 1
    Never, ever store data as comma separated items in one column. It will only cause you lots of trouble. (As you've already noticed...) – jarlh Jun 10 '15 at 06:39

1 Answers1

4

I don't know what application are you design, but I think it was bad design to store values in comma separated instead of create a table details. You can solved this without using a mysql function actually. First, you need to convert comma separated columns into rows and then you can do some calculation. This query may help you :

select id,max(val) as max,min(val) as min,sum(val) as sum,avg(val) as avg
from(
    select id,(substring_index(substring_index(t.poin, ',', n.n), ',', -1)) val
        from poin_dtl t cross join(
         select a.n + b.n * 10 + 1 n
         from 
            (select 0 as n union all select 1 union all select 2 union all select 3 
                union all select 4 union all select 5 union all select 6 
                union all select 7 union all select 8 union all select 9) a,
            (select 0 as n union all select 1 union all select 2 union all select 3 
                union all select 4 union all select 5 union all select 6 
                union all select 7 union all select 8 union all select 9) b
            order by n 
        ) n <-- To make this simple, Create a table with one column that has 100 rows.
    where n.n <= 1 + (length(t.poin) - length(replace(t.poin, ',', '')))
    order by val asc
) as c_rows -- c_rows = convert comma separated columns into rows
group by id

The results should be like this:

id     max     min      sum      avg
--------------------------------------
1      1       9        23        4,6
2      8       2        19        4,75
3      9       1        33        5,5
Hotdin Gurning
  • 1,821
  • 3
  • 15
  • 24