CODEWITHSUNDEEP
mysqlsqlgroup-byaggregate-functions
Azima
Azima

Reputation: 4141

get a single aggregated row mysql

I have a following data-set:

project_type_id  |   total_hours   |  task_completion
-------------------------------------------------------
10               |   5             |  2018-9-10
10               |   4             |  2018-9-11
11               |   10            |  2018-9-10
12               |   2             |  2018-9-10
13               |   9             |  2018-9-10
14               |   8             |  2018-9-11

I am trying to get total sum of hours for each available project_type in the data-set for given year input.

Here's my query:

DROP PROCEDURE IF EXISTS getTaskHourReport;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `getTaskHourReport`(IN `year` INT)
BEGIN

SET GLOBAL group_concat_max_len=4294967295;
SET @SQL = NULL;
SET @year = year;

    SELECT
    COALESCE(GROUP_CONCAT(DISTINCT
    CONCAT(
    'SUM(CASE WHEN project_type_id = "',project_type_id,'" THEN total_hours ELSE 0 END) AS `',project_type_id,'`'
    )
), '0 as `NoMatchingRows`') INTO @SQL

        FROM `task_details`
        WHERE YEAR(task_completion) = @year;     

    SET @SQL 
    = CONCAT
    (
    '
        SELECT ', @SQL, ' 
        FROM
        task_details
        WHERE YEAR(task_completion) = @year
        GROUP BY project_type_id
    '
    );

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;


END$$
DELIMITER ;

This query is giving me following output:

10  |  11  |  12  |  13  |  14
-------------------------------
9   |  0   |  0   |  0   |  0
0   |  10  |  0   |  0   |  0
0   |  0   |  2   |  0   |  0
0   |  0   |  0   |  9   |  0
0   |  0   |  0   |  0   |  8

Expected output:

10  |  11  |  12  |  13  |  14
-------------------------------
9   |  10  |  2   |  9   |  8

How can I get a single row as expected?

Upvotes: 1

Views: 38

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

Remove the GROUP BY. So:

SET @SQL = CONCAT('SELECT ', @SQL, ' ',
                  'FROM task_details '
                  'WHERE YEAR(task_completion) = @year'
                 );

An aggregation query with no GROUP BY treats all the rows as a single group. So, it returns exactly one row. This is true even if the referenced table is empty or the filtering conditions remove all rows (in that case, most aggregation functions return NULL although COUNT() returns 0).

Upvotes: 3

Related Questions