Using MySQL's group_concat function to realize aggregation multiplication

The MySQL aggregate function provides a plus, an average, a minimum, a maximum, etc., but does not provide multiplication, and here we use MySQL's existing group_concat function to achieve aggregate multiplication.
Create a sample table first:

CREATE TABLE ' tb_seq ' (  ' num ' int () not null,  ' Seq_type ' enum (' Yellow ', ' green ', ' red ') is not null) Engine=innodb D Efault Charset=utf8;

Insert Sample data:

Insert INTO  ' tb_seq ' (' num ', ' seq_type ') VALUES (4, ' green '), (1, ' Red '), (3, ' green '), (1, ' Red '), (8, ' Red '), (4, '    Yellow '),    (8, ' Red '), (7, ' Yellow '), (Ten, ' Red '), (1, ' Red '), (1, ' Red '), (1, ' Yellow '), (5, '    Green '), (9, '    red '), (1, ' yellow '),    (6, ' yellow ');

Creates a string multiplication based on a comma delimiter, provided that the string is separated by a comma by a number.

DELIMITER $ $USE ' t_girl ' $ $DROP function IF EXISTS ' func_multiple ' $ $CREATE definer= ' root ' @ ' localhost ' FUNCTION ' func_ Multiple ' (    f_nums VARCHAR)    RETURNS DOUBLE (10,2) BEGIN      -Created by Ytt 2014/10/21.      DECLARE result DOUBLE (10,2) DEFAULT 1;      DECLARE cnt,i INT DEFAULT 0;            SET cnt = char_length (f_nums)-Char_length (REPLACE (f_nums, ', ', ')) + 1;            While I < CNT      do        -get multiple result.        SET result = result * REVERSE (Substring_index (REVERSE (Substring_index (f_nums, ', ', i+1)), ', ', 1));        SET i = i + 1;      END while;      SET result = ROUND (result,2);      RETURN result;       end$ $DELIMITER;

Well, we use the functions I've created and the Group_concat aggregation functions from MySQL to make it easy to multiply.

SELECT seq_type,func_multiple (Group_concat (num ORDER by num ASC SEPARATOR ', ')) as Multiple_num from Tb_seq WHERE 1 GROUP by seq_type;+----------+--------------+| Seq_type | Multiple_num |+----------+--------------+| Yellow   |       168.00 | | Green    |        60.00 | | Red      |      5760.00 |+----------+--------------+3 rows in Set (0.00 sec)

Using MySQL's group_concat function to realize aggregation multiplication