Using GROUP BY after calling a stored procedure in MySQL

Question

I want to do something like this:

CALL testFunction('26e4ccaa-b125-3d9c-c7c1-58175227a48f')
GROUP BY id_c;

This doesn't seem to be working, so I tried this as well:

SELECT *
FROM (
   CALL testFunction('26e4ccaa-b125-3d9c-c7c1-58175227a48f')
) t
GROUP BY t.id_c;

And that doesn't work either. I can find no documentation on if this is or isn't possible.

I am using this stored procedure elsewhere, and it seemed redundant to create a whole new stored procedure just so I can include a GROUP BY. I am calling this stored procedure inside of a php application.

EDIT:

Here is the stored procedure:

BEGIN
    DECLARE lastInsertCount INT;
    DECLARE lastLayer INT;
    DROP TABLE IF EXISTS `temp`;
    DROP TABLE IF EXISTS `productGrid`;

    CREATE TABLE `temp`
    (
        `layer` INT(5),
        `title_c` VARCHAR(255), 
        `name` VARCHAR(255), 
        `rep_first_name` VARCHAR(255), 
        `rep_last_name` VARCHAR(255),
        `id_c` CHAR(36),
        `contact_status_reps_c` VARCHAR(255),
        `phone_mobile` VARCHAR(255),
        `contract_type_c` VARCHAR(255),
        `email_address` VARCHAR(255),
        `active_as_of_c` DATETIME,
        `assigned_user_id` CHAR(36)
    );

    CREATE TABLE `productGrid`
    (
        `percentage_c` INT(5),
        `name` VARCHAR(255), 
        `title_c` VARCHAR(255)
    );

    INSERT INTO `productGrid`(`percentage_c`, `name`, `title_c`)
    SELECT aaigloba_crm.pcg_product_comp_grid_cstm.percentage_c, 
           aaigloba_crm.aos_products.name, 
           aaigloba_crm.pcg_product_comp_grid_cstm.title_c
    FROM aaigloba_crm.aos_products_pcg_product_comp_grid_1_c 
        LEFT OUTER JOIN aaigloba_crm.aos_products ON aaigloba_crm.aos_products_pcg_product_comp_grid_1_c.aos_products_pcg_product_comp_grid_1aos_products_ida = aaigloba_crm.aos_products.id
        LEFT OUTER JOIN aaigloba_crm.pcg_product_comp_grid_cstm ON aaigloba_crm.aos_products_pcg_product_comp_grid_1_c.aos_products_pcg_product_comp_grid_1pcg_product_comp_grid_idb = aaigloba_crm.pcg_product_comp_grid_cstm.id_c
    WHERE aaigloba_crm.aos_products_pcg_product_comp_grid_1_c.deleted = '0';

    INSERT INTO `temp`(`layer`, `title_c`, `name`, `rep_first_name`, `rep_last_name`, `id_c`, `contact_status_reps_c`, `phone_mobile`,`contract_type_c`,`email_address`,`active_as_of_c`,`assigned_user_id`)
    SELECT 0,
    wn_writing_number_cstm.title_c, 
    wn_writing_number.`name`, 
    preps_contracted_reps.first_name, 
    preps_contracted_reps.last_name, 
    wn_writing_number_cstm.id_c, 
    preps_contracted_reps_cstm.contact_status_reps_c, 
    preps_contracted_reps.phone_mobile, 
    preps_contracted_reps_cstm.contract_type_c, 
    email_addresses.email_address,
    STR_TO_DATE(preps_contracted_reps_cstm.active_as_of_c, '%m/%d/%Y'),
    wn_writing_number.assigned_user_id
FROM wn_writing_number LEFT OUTER JOIN wn_writing_number_cac_customize_agent_comp_1_c ON wn_writing_number_cac_customize_agent_comp_1_c.wn_writing946b_number_ida = wn_writing_number.id
     LEFT OUTER JOIN cac_customize_agent_comp ON wn_writing_number_cac_customize_agent_comp_1_c.wn_writing3148nt_comp_idb = cac_customize_agent_comp.id
     LEFT OUTER JOIN cac_customize_agent_comp_cstm ON cac_customize_agent_comp.id = cac_customize_agent_comp_cstm.id_c
     LEFT OUTER JOIN aos_products_cac_customize_agent_comp_1_c ON cac_customize_agent_comp_cstm.id_c = aos_products_cac_customize_agent_comp_1_c.aos_produca2b8nt_comp_idb
     LEFT OUTER JOIN preps_contracted_reps_wn_writing_number_1_c ON preps_contracted_reps_wn_writing_number_1_c.preps_contracted_reps_wn_writing_number_1wn_writing_number_idb = wn_writing_number.id
     LEFT OUTER JOIN preps_contracted_reps ON preps_contracted_reps_wn_writing_number_1_c.preps_cont9effed_reps_ida = preps_contracted_reps.id
     LEFT OUTER JOIN preps_contracted_reps_cstm ON preps_contracted_reps.id = preps_contracted_reps_cstm.id_c
     LEFT OUTER JOIN email_addr_bean_rel ON email_addr_bean_rel.bean_id = preps_contracted_reps_cstm.id_c
     LEFT OUTER JOIN email_addresses ON email_addr_bean_rel.email_address_id = email_addresses.id
     LEFT OUTER JOIN wn_writing_number_cstm ON wn_writing_number_cstm.id_c = wn_writing_number.id
    WHERE wn_writing_number_cstm.wn_writing_number_id_c = passId AND wn_writing_number.deleted = '0' AND preps_contracted_reps.deleted = '0' AND wn_writing_number.deleted = '0' AND email_addr_bean_rel.deleted = '0'
    GROUP BY wn_writing_number.`name`;

    SET lastLayer := 0;
    SET lastInsertCount := 1;
    LayerLoop: WHILE lastInsertCount > 0 DO
      INSERT INTO `temp`(`layer`, `title_c`, `name`, `rep_first_name`, `rep_last_name`, `id_c`, `contact_status_reps_c`, `phone_mobile`,`contract_type_c`,`email_address`,`active_as_of_c`,`assigned_user_id`)
      SELECT lastLayer + 1,
        wn_writing_number_cstm.title_c, 
        wn_writing_number.`name`, 
        preps_contracted_reps.first_name, 
        preps_contracted_reps.last_name, 
        wn_writing_number_cstm.id_c,
        preps_contracted_reps_cstm.contact_status_reps_c, 
        preps_contracted_reps.phone_mobile, 
        preps_contracted_reps_cstm.contract_type_c, 
        email_addresses.email_address,
        STR_TO_DATE(preps_contracted_reps_cstm.active_as_of_c, '%m/%d/%Y'),
        wn_writing_number.assigned_user_id
    FROM wn_writing_number LEFT OUTER JOIN wn_writing_number_cac_customize_agent_comp_1_c ON wn_writing_number_cac_customize_agent_comp_1_c.wn_writing946b_number_ida = wn_writing_number.id
         LEFT OUTER JOIN cac_customize_agent_comp ON wn_writing_number_cac_customize_agent_comp_1_c.wn_writing3148nt_comp_idb = cac_customize_agent_comp.id
         LEFT OUTER JOIN cac_customize_agent_comp_cstm ON cac_customize_agent_comp.id = cac_customize_agent_comp_cstm.id_c
         LEFT OUTER JOIN aos_products_cac_customize_agent_comp_1_c ON cac_customize_agent_comp_cstm.id_c = aos_products_cac_customize_agent_comp_1_c.aos_produca2b8nt_comp_idb
         LEFT OUTER JOIN preps_contracted_reps_wn_writing_number_1_c ON preps_contracted_reps_wn_writing_number_1_c.preps_contracted_reps_wn_writing_number_1wn_writing_number_idb = wn_writing_number.id
         LEFT OUTER JOIN preps_contracted_reps ON preps_contracted_reps_wn_writing_number_1_c.preps_cont9effed_reps_ida = preps_contracted_reps.id
         LEFT OUTER JOIN preps_contracted_reps_cstm ON preps_contracted_reps.id = preps_contracted_reps_cstm.id_c
         LEFT OUTER JOIN email_addr_bean_rel ON email_addr_bean_rel.bean_id = preps_contracted_reps_cstm.id_c
         LEFT OUTER JOIN email_addresses ON email_addr_bean_rel.email_address_id = email_addresses.id
         LEFT OUTER JOIN wn_writing_number_cstm ON wn_writing_number_cstm.id_c = wn_writing_number.id
      WHERE preps_contracted_reps.deleted = '0' AND wn_writing_number.deleted = '0' AND email_addr_bean_rel.deleted = '0' AND wn_writing_number_cstm.wn_writing_number_id_c IN (SELECT id_c FROM `temp` WHERE layer = lastLayer)
      GROUP BY wn_writing_number.`name`;

      SET lastInsertCount := ROW_COUNT();
      SET lastLayer := lastLayer + 1;
    END WHILE LayerLoop;

    SELECT `title_c`, `name`, `rep_first_name`, `rep_last_name`, `id_c`, `contact_status_reps_c`, `phone_mobile`,`contract_type_c`,`email_address`,`active_as_of_c`,`assigned_user_id`
    FROM `temp`
    ORDER BY rep_first_name ASC;
END

Possible duplicate of [Can I Use Order by to sort Stored Procedure results?](http://stackoverflow.com/questions/4023798/can-i-use-order-by-to-sort-stored-procedure-results) — sbharti, May 16 '17 at 18:01
you need to insert data in a table first. then u can do operations on the data. see above link. — sbharti, May 16 '17 at 18:01
I am already storing everything to a temporary table inside the stored procedure and then using a select of the temp table at the end of stored procedure. My question is specifically, can you or can you not use a GROUP BY outside of the stored procedure. — Josh Whitlow, May 16 '17 at 18:17
Where is the problem? If your function return rows you can do `SELECT * FROM yourFunction()` — Juan Carlos Oropeza, May 16 '17 at 18:25
What? `SELECT * FROM testFunction('26e4ccaa-b125-3d9c-c7c1-58175227a48f')` does not work in MySQL. — Josh Whitlow, May 16 '17 at 18:35

dkretz · Accepted Answer · 2017-05-17T18:00:41.010

1

The GROUP clause is an SQL clause, and needs to be incorporated into a full SQL expression because the SQL parser optimizes and executes whole expressions atomically. The function returns a table, not an SQL expression, and an SQL clause can't modify a table.

Using temp tables is another sign you're conceptually decomposing your operation, probably deoptimizing it. Decomposition is a good pattern for procedural code, but it's an antipattern for declarative code.

You probably already know that the WHILE loop is problematic in an operation on relational data. Too bad you're not using isam tables.

edited May 17 '17 at 18:00

answered May 17 '17 at 16:58

dkretz

37,399
13
80
138

I think this answers my question. "**The function returns a table, not an SQL expression, and an SQL clause can't modify a table.**" So, no I cannot use a `GROUP BY` statement after a `CALL` statement especially when my function is returning a table. Which means I either have to change my stored procedure, create a new one, or handle the grouping inside of php. I think I'll do the 3rd option. – Josh Whitlow May 17 '17 at 17:53

Juan Carlos Oropeza · Answer 2 · 2017-05-16T18:42:06.283

0

Your function need to return a set of records.

CREATE OR REPLACE FUNCTION testFunction(field1 OUT text, field2 OUT INT)
RETURNS setof record
AS $$ SELECT field1, field2 FROM yourTable $$
LANGUAGE sql;

Then you can call your function

SELECT field1, SUM(field2)
FROM testFunction()
GROUP BY field1

Check this DEMO

CREATE OR REPLACE FUNCTION dup1(int) 
RETURNS TABLE( f1 text, f2 int) AS 
$$ 
   select f1::text, f2
   FROM ( VALUES ('apple', 3), ('apple', 2), 
                 ('mango', 1), ('pineapple', 3), 
                 ('pineapple', 5) ) T(f1, f2)
$$
LANGUAGE sql;

SELECT f1, SUM(f2)
FROM dup1(1)
GROUP BY f1;

OUTPUT

edited May 16 '17 at 18:42

answered May 16 '17 at 18:23

Juan Carlos Oropeza

47,252
12
78
118

My function does return records. And while I am aware I could do another select, I was trying see if it was possible to do a `GROUP BY` on the existing result set I already had instead of doing another select. I would need to call the function each time, ignore the results, and then make this seperate select so that I could group the results. If there isn't, then IMO, its better to just make a seperate stored procedure that includes the `GROUP BY` and use that instead. – Josh Whitlow May 16 '17 at 18:37
I'm not sure what is the problem. I change to `RETURN TABLE` sintaxis. Function return data and you can use `GROUP BY` – Juan Carlos Oropeza May 16 '17 at 18:42

Using GROUP BY after calling a stored procedure in MySQL

2 Answers2