title: How to find the total after using count and group_contat
body: <p>So I was making a table that shows the total, but the total amount is not correct.</p>
  <p>SQL Fiddle: <a href="http://sqlfiddle.com/#!9/de76b6e/12" rel="nofollow noreferrer">http://sqlfiddle.com/#!9/de76b6e/12</a></p>
  <p>Expected output:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ClientName</th>
  <th>ClientPhone</th>
  <th>Address</th>
  <th>Services</th>
  <th>Total</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Chan Tai Man</td>
  <td>12345678</td>
  <td>82 Kennedy Street, Wan Chai</td>
  <td>Carpets(2),Sofas(1)</td>
  <td>1120</td>
  </tr>
  <tr>
  <td>Wong Kai tung</td>
  <td>28473465</td>
  <td>24 Kennedy Road, Wan Chai</td>
  <td>Mattresses(1)</td>
  <td>100</td>
  </tr>
  <tr>
  <td>Chan Tai Man</td>
  <td>63827482</td>
  <td>26 Queen Road East, Wan Chai</td>
  <td>Carpets(1)</td>
  <td>500</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Actual output:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>ClientName</th>
  <th>ClientPhone</th>
  <th>Address</th>
  <th>Services</th>
  <th>Total</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>Chan Tai Man</td>
  <td>12345678</td>
  <td>82 Kennedy Street, Wan Chai</td>
  <td>Carpets(2),Sofas(1)</td>
  <td>620</td>
  </tr>
  <tr>
  <td>Wong Kai tung</td>
  <td>28473465</td>
  <td>24 Kennedy Road, Wan Chai</td>
  <td>Mattresses(1)</td>
  <td>100</td>
  </tr>
  <tr>
  <td>Chan Tai Man</td>
  <td>63827482</td>
  <td>26 Queen Road East, Wan Chai</td>
  <td>Carpets(1)</td>
  <td>500</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>My Data:</p>
  <pre><code>
  INSERT INTO MAIN VALUES( 
      null,'Carpets', '2021-03-09', '1');  
  INSERT INTO MAIN VALUES( 
      null,'Sofas', '2021-03-09', '1');
  INSERT INTO MAIN VALUES( 
      null,'Mattresses', '2021-03-10', '2');
  INSERT INTO MAIN VALUES( 
      null,'Carpets', '2021-03-11', '3');
  INSERT INTO MAIN VALUES( 
      null,'Carpets', '2021-03-11', '1');
  
    
   
  /*****  CREATE SERVICE  **************************************************/
  DROP TABLE IF EXISTS SERVICE;
  CREATE TABLE SERVICE(
    ServiceName                       VarChar(20)   NOT NULL,
    CPS                               Numeric(20)   NOT NULL,
    CONSTRAINT          SERVICE_PK            PRIMARY KEY(ServiceName)
      );
  
  /*****   SERVICE DATA   ***********************************************************/
  
  INSERT INTO SERVICE VALUES('Carpets', 500);
  INSERT INTO SERVICE VALUES('Sofas', 120);
  INSERT INTO SERVICE VALUES('Mattresses', 100);
  INSERT INTO SERVICE VALUES('Curtains', 80);
  
  
  
  /*****   CREATE CLIENT   ***********************************************************/
  DROP TABLE IF EXISTS CLIENT;
  CREATE TABLE CLIENT (
      ClientID                            Int      auto_increment  NOT NULL,
      ClientName                      Char (50)           NOT NULL,
      ClientPhone                              Int (8)                    NOT NULL,
      Address                              VarChar(100)               NULL,
      CONSTRAINT        CLIENT_PK             PRIMARY KEY(ClientID)
      );
  
  /*****   CLIENT DATA   *************************************************************/
  
  INSERT INTO CLIENT VALUES(
      null,'Chan Tai Man', '12345678', '82 Kennedy Street, Wan Chai');
  INSERT INTO CLIENT VALUES(
      null,'Wong Kai tung', '28473465', '24 Kennedy Road, Wan Chai');
  INSERT INTO CLIENT VALUES(
      null,'Chan Tai Man', '63827482', '26 Queen Road East, Wan Chai');
  
  /***********************************************************************************/
  </code></pre>
  <p>My code:</p>
  <pre><code>SELECT CLIENT.ClientName, CLIENT.ClientPhone, CLIENT.Address, 
  GROUP_CONCAT(MAIN.Num) AS Services,
  SUM(SERVICE.CPS) AS Total
  FROM (
    SELECT DOS, InvoiceID, ClientID, ServiceName, 
    CONCAT(ServiceName, '(', count(ServiceName), ')') AS Num
    From MAIN
    GROUP BY ClientID, ServiceName
    ) MAIN
  LEFT JOIN CLIENT ON MAIN.ClientID = CLIENT.ClientID 
  LEFT JOIN SERVICE ON MAIN.ServiceName = SERVICE.ServiceName
  GROUP BY CLIENT.ClientID
  ORDER BY MAIN.DOS;
  </code></pre>