ujwal dhakal February 2016

Mysql group by with normal column and mysql function aliases column

I have been trying to group by the mysql group by in normal column and mysql function aliases column where the mysql function aliases wont work group by The query i tried and which should work according to the mysql documentation.

SELECT `project`.`project_name`, SUM(cost_allocated) AS cost_allocated,
    count(task_id) as countTask, 
    GROUP_CONCAT(task_registration.members SEPARATOR ';') AS members,     
    GROUP_CONCAT(task_registration.indicators SEPARATOR ';') AS indicators, 
    GROUP_CONCAT(task_registration.percent_complete SEPARATOR ';') AS percent_complete,
    GROUP_CONCAT(task_registration.status SEPARATOR ';') AS status, 
    DATE_FORMAT(`task_registration`.`created_at`, '%M') AS month
FROM (`project`) 
INNER JOIN `task_registration` ON `task_registration`.`project` = `project`.`project_id` 
GROUP BY `task_registration`.`project`,`month` 
ORDER BY `project`.`project_id` desc

The result i get enter image description here

Now after removing the normal column group by and just adding mysql function aliases column it works fine but i need both

SELECT `project`.`project_name`, SUM(cost_allocated) AS cost_allocated, 
    count(task_id) as countTask, 
    GROUP_CONCAT(task_registration.members SEPARATOR ';') AS members, 
    GROUP_CONCAT(task_registration.indicators SEPARATOR ';') AS indicators, 
    GROUP_CONCAT(task_registration.percent_complete SEPARATOR ';') AS percent_complete, 
    GROUP_CONCAT(task_registration.status SEPARATOR ';') AS status, 
    DATE_FORMAT(`task_registration`.`created_at`, '%M') AS month
FROM (`project`) 
INNER JOIN `task_registration` ON `task_registration`.`project` = `project`.`project_id` 
GROUP BY `month` 
ORDER BY `project`.`project_id` desc

after removing task_registration`.`project from group by

Answers


Kickstart February 2016

Very difficult to work out what you want without table declarations, sample input data and sample output data, but from your comment all I can suggest is using the 2nd query as a sub query and joining it to the results of the first query:-

SELECT project.project_name, 
    SUM(cost_allocated) AS cost_allocated,
    count(task_id) as countTask, 
    GROUP_CONCAT(task_registration.members SEPARATOR ';') AS members,     
    GROUP_CONCAT(task_registration.indicators SEPARATOR ';') AS indicators, 
    GROUP_CONCAT(task_registration.percent_complete SEPARATOR ';') AS percent_complete,
    GROUP_CONCAT(task_registration.status SEPARATOR ';') AS status, 
    DATE_FORMAT(task_registration.created_at, '%M') AS month,
    sub0.cost_allocated AS months_cost_allocated, 
    sub0.countTask AS months_countTask, 
    sub0.members AS months_members, 
    sub0.indicators AS months_indicators, 
    sub0.percent_complete AS months_percent_complete, 
    sub0.status AS months_status
FROM (`project`) 
INNER JOIN `task_registration` ON `task_registration`.`project` = `project`.`project_id` 
INNER JOIN
(
    SELECT DATE_FORMAT(task_registration.created_at, '%M') AS month,
        SUM(cost_allocated) AS cost_allocated, 
        COUNT(task_id) as countTask, 
        GROUP_CONCAT(task_registration.members SEPARATOR ';') AS members, 
        GROUP_CONCAT(task_registration.indicators SEPARATOR ';') AS indicators, 
        GROUP_CONCAT(task_registration.percent_complete SEPARATOR ';') AS percent_complete, 
        GROUP_CONCAT(task_registration.status SEPARATOR ';') AS status
    FROM (project) 
    INNER JOIN task_registration ON task_registration.project = project.project_id
    GROUP BY `month` 
) sub0
ON DATE_FORMAT(task_registration.created_at, '%M') = sub0.`month`
GROUP BY task_registration.project,
        `month`, 
        months_cost_allocated, 
        sub0.countTask AS months_countTask, 
        sub0.members AS months_members, 
        sub0.indicators AS months_ind 


ujwal dhakal February 2016

Thank you for the answer i did it on my own

   SELECT COUNT( i.project_name ) AS completed_projects,i.month 
FROM (

SELECT `project`.`project_name` , SUM( cost_allocated ) AS cost_allocated, COUNT( task_id ) AS countTask, GROUP_CONCAT( task_registration.members
SEPARATOR  ';' ) AS members, GROUP_CONCAT( task_registration.indicators
SEPARATOR  ';' ) AS indicators, GROUP_CONCAT( task_registration.percent_complete
SEPARATOR  ';' ) AS percent_complete, GROUP_CONCAT( task_registration.status
SEPARATOR  ';' ) AS 
STATUS , DATE_FORMAT( task_registration.created_at,  '%M' ) AS 
MONTH FROM (
`project`
)
INNER JOIN  `task_registration` ON  `task_registration`.`project` =  `project`.`project_id` 
GROUP BY  `task_registration`.`project` 
ORDER BY  `project`.`project_id` DESC
) AS i
GROUP BY i.month

http://sqlfiddle.com/#!2/18389/11

Post Status

Asked in February 2016
Viewed 2,391 times
Voted 8
Answered 2 times

Search




Leave an answer