How to do aggregate functions in GROUP_CONCAT I am generating a report to show outstanding Fee Like which Fee Type, How much amount is Fixed for that Fee Type, How Much Amount Paid for that Fee type and finally Balance Amount to be paid for that fee Type.

Here I am pulling data from 5 tables like Class Names from Classes table, Roll no &amp; Student Name from admissions table, Fee Types from Feevars table, amount fixed for fee type from studentfees table, and finally amounts paid for fee types from fee collection table.

I am able to generate the partial results by mentioning the fee type names in select statment by summing and subtracting operations.

Here is the full database and my query producing the result. Please review  **@ my query in this demo, in the select statement I have mentioned the fee types manually.
But I want to generate the result without mentioning the Fee type as Column names.

For this I did one thing, I had taken all the fee types into a sql variable
like this

set @sqlList = null;SELECT GROUP_CONCAT(concat('tsf.', Replace(FeeName,' ',''))) INTO @sqlList FROM tbl_feevars;


this will result all the Fee types into single line as column names.
And Finally I have written code to produce output what I am expecting, but I am getting error like Error Code 1064: You have error in your sql syntax.

This is My final code

Expected Output Code

Please anyone tell me, what is error in my sql query. And suggest me if any?? other way to do this report.

My Tables:

CREATE TABLE `tbl_admission` (
  `AdmNo` varchar(50) NOT NULL,
  `Grp` varchar(45) NOT NULL,
  `Cls` varchar(45) NOT NULL,
  `rollno` int(11) NOT NULL,  
  `StdNm` varchar(50)
);

CREATE TABLE `tbl_classes` (
  `C_Id` int(11),
  `C_Name` varchar(255) NOT NULL,
  `G_Id` int(11) NOT NULL,
  `status` tinyint(1)
);

CREATE TABLE `tbl_feevars` (
  `id` int(11) NOT NULL,
  `FeeName` varchar(70) NOT NULL
);

CREATE TABLE `tbl_stdfees` (
  `id` int(11),
  `Admno` varchar(50) default NULL,
  `admissionfee` float(10,2) default '0.00',
  `splfee` float(10,2) default '0.00',
  `tutionfee` float(10,2) default '0.00',
  `computerfee` float(10,2) default '0.00',
  `stationary` float(10,2) default '0.00',
  `transport` float(10,2) default '0.00',
  `hostelfee` float(10,2) default '0.00',
  `lab` float(10,2) default '0.00',
  `library` float(10,2) default '0.00',
  `miscell` float(10,2) default '0.00'
  );

CREATE TABLE `tbl_feecollection` (
  `auto_id` int(11) NOT NULL auto_increment,
  `GrupNm` varchar(40) NOT NULL,
  `class` varchar(30) NOT NULL,
  `rollno` int(2) NOT NULL,
  `sadmno` int(11) NOT NULL,
  `PaM` varchar(30) NOT NULL,
  `DDCN` int(25) NOT NULL,
  `bank` varchar(30) NOT NULL,
  `cheqd` date NOT NULL,
  `remarks` varchar(150) NOT NULL,
  `pdate` date NOT NULL,
  `admissionfee` float(10,2) default '0.00',
  `splfee` float(10,2) default '0.00',
  `tutionfee` float(10,2) default '0.00',
  `computerfee` float(10,2) default '0.00',
  `stationary` float(10,2) default '0.00',
  `transport` float(10,2) default '0.00',
  `hostelfee` float(10,2) default '0.00',
  `lab` float(10,2) default '0.00',
  `library` float(10,2) default '0.00',
  `miscell` float(10,2) default '0.00',
  PRIMARY KEY  (`auto_id`)
);


My current query is:

set @sqlList = null;
SET @sqlList1 = null;
set @query = null;
SELECT
  GROUP_CONCAT(concat('tsf.', Replace(FeeName,' ',''))) 
INTO @sqlList
FROM tbl_feevars;
SELECT
  GROUP_CONCAT(concat('tfc.', Replace(FeeName,' ',''))) 
INTO @sqlList1
FROM tbl_feevars;
SET @query 
  = CONCAT('SELECT tsf.Admno,',@sqlList,',ta.StdNm,
                    sum(',@sqlList1,') as ''feepaid'',
                    (',@sqlList,' - sum(',@sqlList1,')) as ''outstanding fee''
                FROM
                    tbl_stdfees tsf,
                    tbl_Admission ta,
                    tbl_feecollection tfc
                where
                    tsf.admno = ta.admno
                    and tfc.sadmno = tsf.admno group by ta.stdnm);');


select @query;

PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Your query (the one that doesn't work) is:

   SELECT
      tsf.Admno,tsf.AdmissionFee,tsf.SplFee,
      tsf.TutionFee,tsf.ComputerFee,tsf.Stationary,
      tsf.Transport,tsf.Hostelfee,tsf.Lab,tsf.Library,
      tsf.Miscell,ta.StdNm,
      SUM(
          tfc.AdmissionFee,tfc.SplFee,tfc.TutionFee,
          tfc.ComputerFee,tfc.Stationary,tfc.Transport,
          tfc.Hostelfee,tfc.Lab,tfc.Library,tfc.Miscell ) AS 'feepaid',
      (SUM(
          tsf.Admno,tsf.AdmissionFee,tsf.SplFee,
          tsf.TutionFee,tsf.ComputerFee,tsf.Stationary,
          tsf.Transport,tsf.Hostelfee,tsf.Lab,tsf.Library,
          tsf.Miscell
          ) -
       SUM(
          tsf.Admno,tsf.AdmissionFee,tsf.SplFee,
          tsf.TutionFee,tsf.ComputerFee,tsf.Stationary,
          tsf.Transport,tsf.Hostelfee,tsf.Lab,tsf.Library,
          tsf.Miscell
          )
      ) AS 'outstanding fee'
      FROM tbl_stdfees tsf,
           tbl_admission ta,
           tbl_feecollection tfc
      WHERE
           tsf.admno = ta.admno
           AND tfc.sadmno = tsf.admno
      GROUP BY ta.stdnm;


You are using SUM, an aggregate function that sums up values in the same column, but you are just adding up the individual column values in one row - so MySQL gives you an error message. If you change the query as below, it will work. 

SELECT DISTINCT
      tsf.Admno,tsf.AdmissionFee,tsf.SplFee,
      tsf.TutionFee,tsf.ComputerFee,tsf.Stationary,
      tsf.Transport,tsf.Hostelfee,tsf.Lab,tsf.Library,
      tsf.Miscell,ta.StdNm,
      tfc.AdmissionFee + tfc.SplFee + tfc.TutionFee +
      tfc.ComputerFee + tfc.Stationary + tfc.Transport +
      tfc.Hostelfee + tfc.Lab + tfc.Library + tfc.Miscell AS 'feepaid',
      ( (tsf.Admno + tsf.AdmissionFee + tsf.SplFee +
          tsf.TutionFee + tsf.ComputerFee + tsf.Stationary +
          tsf.Transport + tsf.Hostelfee + tsf.Lab + tsf.Library +
          tsf.Miscell)
          -
          (tfc.AdmissionFee + tfc.SplFee + tfc.TutionFee +
          tfc.ComputerFee + tfc.Stationary + tfc.Transport +
          tfc.Hostelfee + tfc.Lab + tfc.Library + tfc.Miscell)
        ) AS 'outstanding fee'
      FROM tbl_stdfees tsf,
           tbl_admission ta,
           tbl_feecollection tfc
      WHERE
           tsf.admno = ta.admno
           AND tfc.sadmno = tsf.admno;


Since you are not using an aggregate function, you don't need to use GROUP BY either. 

Result:

  Admno | AdmissionFee | SplFee | TutionFee | ComputerFee | Stationary | Transport | Hostelfee | Lab  | Library | Miscell | StdNm | feepaid | outstanding fee
  1     |      6000.00 |   0.00 |      0.00 |     2500.00 |    1500.00 |      0.00 |      0.00 | 0.00 |    0.00 | 3000.00 | zzz   | 1400.00 |           11601 
  2     |      5000.00 |   0.00 |      0.00 |     2500.00 |    1500.00 |      0.00 |      0.00 | 0.00 |    0.00 | 5000.00 | xxyy  | 1500.00 |           12502 |
  2 rows in set (0.01 sec)


That being said: 
In my experience the database is much easier to maintain, if you'd restructure the tables and normalize them, have one row for each of the fees, and then use SUM and GROUP BY. You then could add fee types without changing the table structure, and your scripts don't need to know which fees to expect. 

For example, change the tbl_stdfees to:

CREATE TABLE tbl_newfees ( 
   id INT AUTO_INCREMENT,          
   Admno VARCHAR(50) NOT NULL,          
   fee_id int, 
   fee FLOAT(10,2) DEFAULT 0.00,      
   PRIMARY KEY (id, fee_id, fee), 
   CONSTRAINT FOREIGN KEY (fee_id) REFERENCES tbl_feevars.id);


This assumes that you only have one fee per fee type per id. If several fees might occur of the same type, you could add an additional identifier like a date. Use REPLACE to either insert a new few or overwrite an older fee. 

Not taking in account the date and not making provisions for multiple fees of the same type bothers me, I would take provisions for it in production. 

Also, I wouldn't allow a NULL value for Admno - especially since you are grouping by it (in this scenario). 

Example:

mysql&gt; select * from tbl_newfees;
+----+-------+--------+---------+
| id | Admno | fee_id | fee     |
+----+-------+--------+---------+
|  1 | 1     |      1 | 6000.00 |
|  2 | 1     |      4 | 2500.00 |
|  3 | 1     |      5 | 1500.00 |
|  4 | 1     |     10 | 3000.00 |
|  5 | 2     |     10 | 5000.00 |
|  6 | 2     |      5 | 1500.00 |
|  7 | 2     |      4 | 1500.00 |
|  8 | 2     |      1 | 5000.00 |
+----+-------+--------+---------+


This takes advantage of your tbl_feevars table - you'd have to define the FeeName only once. 

mysql&gt; select Admno,sum(fee) from tbl_newfees group by Admno;
+-------+----------+
| Admno | sum(fee) |
+-------+----------+
| 1     | 13000.00 |
| 2     | 14000.00 |
+-------+----------+


Another table for the paid fees - the meta information should go into a separate table and get joined in, however, if the bank, remarks, PaM, etc are specific for each individual fee, which they might be, the could be in the paid fees table as well. If they are general to the fee type, they could be in the tbl_feevars table. You know your system best. 

mysql&gt; select * from tbl_paidfees;
+----+-------+--------+---------+
| id | Admno | fee_id | fee     |
+----+-------+--------+---------+
|  1 | 1     |      1 | 6000.00 |
|  3 | 1     |      5 | 1500.00 |
|  4 | 1     |     10 | 3000.00 |
|  5 | 2     |     10 | 5000.00 |
|  6 | 2     |      5 | 1500.00 |
|  8 | 2     |      1 | 5000.00 |
+----+-------+--------+---------+


(this is a bit different from your example data, I just deleted the fee_id 4 entries complared to the other table)

I then select for the sums using a left outer join, which assumes that all outstanding fees have been entered into the table before an entry in the paid fees table has been made:

SELECT n.Admno, 
       SUM(n.fee) AS "Total Fees", 
       SUM(p.fee) AS "Total Paid", 
       SUM(n.fee)-sum(p.fee) as "Outstanding Balance" 
FROM tbl_newfees n 
       LEFT OUTER JOIN tbl_paidfees p 
       ON n.Admno=p.Admno AND p.fee_id=n.fee_id 
GROUP BY n.Admno;


Results:

+-------+------------+------------+---------------------+
| Admno | Total Fees | Total Paid | Outstanding Balance |
+-------+------------+------------+---------------------+
| 1     |   13000.00 |   10500.00 |             2500.00 |
| 2     |   14000.00 |   11500.00 |             2500.00 |
+-------+------------+------------+---------------------+ 


To query for more detailed information for a specific individual, for example, you could do:

SELECT n.Admno, v.FeeName, n.fee AS "Total Fee", p.fee AS "Fee Paid"     
FROM tbl_feevars v,          
     tbl_newfees n LEFT OUTER JOIN tbl_paidfees p    
                   ON n.fee_id=p.fee_id AND n.Admno = p.Admno 
WHERE v.id = n.fee_id 
ORDER BY n.Admno;

+-------+---------------+-----------+----------+
| Admno | FeeName       | Total Fee | Fee Paid |
+-------+---------------+-----------+----------+
| 1     | Admission Fee |   6000.00 |  6000.00 |
| 1     | Computer Fee  |   2500.00 |     NULL |
| 1     | Stationary    |   1500.00 |  1500.00 |
| 1     | Miscell       |   3000.00 |  3000.00 |
| 2     | Admission Fee |   5000.00 |  5000.00 |
| 2     | Computer Fee  |   2500.00 |     NULL |
| 2     | Stationary    |   1500.00 |  1500.00 |
| 2     | Miscell       |   5000.00 |  5000.00 |
+-------+---------------+-----------+----------+


Similarly, you can left join the tbl_feevars table to see which fees have not been entered for a student into any of the fees tables.

To display the results in one row, you could use the pivot table approach, similar to https://stackoverflow.com/questions/17680926/adding-columns-while-inserting-a-row-in-another-table/17681027#17681027, or just query regularly and assemble the result externally in a script.