title: MySql: How to fix mysql query with group_concat with Sum?
body: <p><strong>Requirement:</strong></p>
  
  <p>I am trying to enhance the sql query with group_contact and sum. But I could not succeed.</p>
  
  <p><strong>SQL Query:</strong></p>
  
  <pre><code> SELECT 
      team.application_name AS 'Project',
      regression.automated_test_type AS 'SuiteType',
      SUM(regression.total_testcases) AS 'Total',
      SUM(regression.passed_testcases) AS 'Passed',
      SUM(regression.failed_testcases) AS 'Failed',
      regression.test_environment AS 'Environment'
  FROM
      regression,
      team
  WHERE
      execution_date &gt;= CURDATE() - INTERVAL 7 DAY
          AND regression.automated_test_type &lt;&gt; 'Regression'
          AND regression.report_link NOT LIKE '%Automation_Q002envHc%'
          AND regression.report_link NOT LIKE '%LitVmEnvHealthChecks%'
          AND team.application_id = regression.regression_application_id
          AND team.application_name &lt;&gt; 'Not Mapped'
  GROUP BY regression_application_id , regression.test_environment
  ORDER BY team.application_name ASC , regression.automated_test_type ASC , regression.test_environment ASC;
  </code></pre>
  
  <p><strong>Current Query Output:</strong></p>
  
  <pre><code>----------------------------------------------------------------------
  Project         SuiteType             Total Passed Failed Environment
  ----------------------------------------------------------------------
  Application 1   Health Check            30    22    8     IQA
  Application 2   Build Validation Test   33    21    12    IQA
  Application 2   Build Validation Test   24    11    13    PROD
  Application 3   Build Validation Test   399   321   78    IQA
  </code></pre>
  
  <p><strong>Expected Output</strong></p>
  
  <pre><code>------------------------------------------------------
  Project         SuiteType               Environment
  ------------------------------------------------------
  Application 1   Health Check            IQA-&gt;30,22,8
  Application 2   Build Validation Test   IQA-&gt;33,21,12|PROD-&gt;24,11,13  
  Application 3   Build Validation Test   IQA-&gt;399,321,78
  </code></pre>
  
  <p>I edited the query based on the comments and answers. Still no luck.</p>
  
  <p><strong>EDITED ANSWER BASED ON THE COMMENTS</strong></p>
  
  <pre><code>     SELECT 
      T.Project,
      T.SuiteType,
      GROUP_CONCAT(CONCAT(T.Environment,
                  '-&gt;',
                  T.Passed,
                  ',',
                  T.Failed)
          ORDER BY T.Environment
          SEPARATOR '|') AS 'Environment'
  FROM
      (SELECT 
          team.application_name AS 'Project',
              regression.automated_test_type AS 'SuiteType',
              SUM(regression.passed_testcases) AS 'Passed',
              SUM(regression.failed_testcases) AS 'Failed',
              regression.test_environment AS 'Environment'
      FROM
          regression, team
      WHERE
          execution_date &gt;= CURDATE() - INTERVAL 7 DAY
              AND regression.automated_test_type &lt;&gt; 'Regression'
              AND regression.report_link NOT LIKE '%Automation_Q002envHc%'
              AND regression.report_link NOT LIKE '%LitVmEnvHealthChecks%'
              AND team.application_id = regression.regression_application_id
              AND team.application_name &lt;&gt; 'Not Mapped'
      GROUP BY regression_application_id , regression.test_environment
      ORDER BY team.application_name ASC , regression.automated_test_type ASC , regression.test_environment ASC) T
  GROUP BY T.Project , T.SuiteType
  ORDER BY T.Project , T.SuiteType
  </code></pre>