Aggregate function procedure

Question

I have to make a procedure with two parameters: kol and agg. kol is obviuosly the column and agg is an aggregate function.

Suppose I have the following table:

+---------+---------+
| X (int) | Y (int) |
+---------+---------+
| 5       | 2       |
| 4       | 4       |
+---------+---------+

I want to call the procedure ('X','sum') - then it shows SELECT SUM(x) FROM table so 9. ('Y','avg') shows SELECT AVG(y) FROM table so 3.

I have something, but it doesn't work when it comes to SUM, AVG, MIN and MAX, it only works when counts (for SUM, AVG, MIN and MAX shows 0, don't know why):

-- I skipped some lines in procedure, only this is necessary

BEGIN
  IF(kol IN (select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where 
  TABLE_NAME='table')) THEN 

    -- getting every column for table above
    SELECT (
      CASE 
        WHEN agg='SUM' THEN sum(kol)
        WHEN agg='COUNT' THEN count(kol)
        WHEN agg='AVG' THEN avg(kol)
        WHEN agg='MAX' THEN max(kol)
        WHEN agg='MIN' THEN min(kol)
    END) Result FROM table;
  END IF;
END

Answer 1

You can use a solution like the following:

DELIMITER //

CREATE PROCEDURE testProc (IN col VARCHAR(10), IN agg VARCHAR(10))
  BEGIN
    SET @select = '';

    -- get the select part with the aggregation function.
    -- using UPPER to allow case-insensitive input.
    SELECT CASE 
      WHEN UPPER(agg) = 'SUM' THEN CONCAT('SUM(', col, ')')
      WHEN UPPER(agg) = 'COUNT' THEN CONCAT('COUNT(', col, ')')
      WHEN UPPER(agg) = 'AVG' THEN CONCAT('AVG(', col, ')')
      WHEN UPPER(agg) = 'MAX' THEN CONCAT('MAX(', col, ')')
      WHEN UPPER(agg) = 'MIN' THEN CONCAT('MIN(', col, ')')
    END
    INTO @select;

    -- create and prepare the full statement.
    SET @stmt = CONCAT('SELECT ', @select, ' AS Result FROM table_name');
    PREPARE stmtExec FROM @stmt;

    -- execute the statement.
    EXECUTE stmtExec;
  END//

I tried this solution with your data and get the following results:

CALL testProc('x', 'SUM');      -- 9
CALL testProc('y', 'SUM');      -- 6
CALL testProc('x', 'COUNT');    -- 2
CALL testProc('y', 'COUNT');    -- 2
CALL testProc('x', 'AVG');      -- 4.500
CALL testProc('y', 'AVG');      -- 3.000
CALL testProc('x', 'MAX');      -- 5
CALL testProc('y', 'MAX');      -- 4
CALL testProc('x', 'MIN');      -- 4
CALL testProc('y', 'MIN');      -- 2

You can't use the column parameter as parameter on the aggregate functions. But you can create a string and use it on a prepared statement (see the solution above):

User variables are intended to provide data values. They cannot be used directly in an SQL statement as an identifier or as part of an identifier, such as in contexts where a table or database name is expected, or as a reserved word such as SELECT.

An exception to this principle that user variables cannot be used to provide identifiers, is when you are constructing a string for use as a prepared statement to execute later. In this case, user variables can be used to provide any part of the statement.

source: https://dev.mysql.com/doc/refman/5.7/en/user-variables.html

---

After you provide some additional information in the comments I changed the PROCEDURE to the following solution. There you get the column name and aggregate function on the result too:

DELIMITER //

CREATE PROCEDURE testProc (IN col VARCHAR(10), IN agg VARCHAR(10))
  BEGIN
    SET @select = '';

    -- get the select part with the aggregation function.
    -- using UPPER to allow case-insensitive input.
    SELECT CASE 
      WHEN UPPER(agg) = 'SUM' THEN CONCAT('SUM(', col, ')')
      WHEN UPPER(agg) = 'COUNT' THEN CONCAT('COUNT(', col, ')')
      WHEN UPPER(agg) = 'AVG' THEN CONCAT('AVG(', col, ')')
      WHEN UPPER(agg) = 'MAX' THEN CONCAT('MAX(', col, ')')
      WHEN UPPER(agg) = 'MIN' THEN CONCAT('MIN(', col, ')')
    END
    INTO @select;

    -- create and prepare the full statement.
    SET @stmt = CONCAT('SELECT CONCAT(\'', col, '|', UPPER(agg), '|\', ', @select, ') AS Result FROM table_name');
    PREPARE stmtExec FROM @stmt;

    -- execute the statement.
    EXECUTE stmtExec;
  END//

So I tested this solution again and get the following results now:

CALL testProc('x', 'SUM');      -- x|SUM|9
CALL testProc('y', 'SUM');      -- y|SUM|6
CALL testProc('x', 'COUNT');    -- x|COUNT|2
CALL testProc('y', 'COUNT');    -- y|COUNT|2
CALL testProc('x', 'AVG');      -- x|AVG|4.5000
CALL testProc('y', 'AVG');      -- y|AVG|3.0000
CALL testProc('x', 'MAX');      -- x|MAX|5
CALL testProc('y', 'MAX');      -- y|MAX|4
CALL testProc('x', 'MIN');      -- x|MIN|4
CALL testProc('y', 'MIN');      -- y|MIN|2