Mysql – How to do aggregate functions in GROUP_CONCAT

MySQL

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 & 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;

Best Answer

Well there are a few issues that I see with your existing query. You current sql statement is the following:

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', 
  (tsf.AdmissionFee,tsf.SplFee,tsf.TutionFee,tsf.ComputerFee,tsf.Stationary,tsf.Transport,tsf.Hostelfee,tsf.Lab,tsf.Library,tsf.Miscell - sum(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 
group by ta.stdnm;

The problem is that you are attempting to aggregate multiple columns in your sum(). The sum() can only aggregate one column at a time.

Second, in my opinion your tables tbl_stdfees and tbl_feecollection are poorly designed. You have basically created a spreadsheet in you table to contain the fees required and then the fees that were paid. The problem is if you need to add a new fee you have to add a new column, that demonstrates a poor design.

I don't think that you need to use dynamic SQL to get the result, you need to unpivot or normalize the data in those two tables. To unpivot the data you will use a UNION ALL query to convert the multiple columns into multiple rows.

The query to unpivot the data would be similar to the following:

tbl_stdfees:

select admno, 'admissionfee' col, admissionfee value
from tbl_stdfees
union all
select admno, 'splfee' col, splfee value
from tbl_stdfees
union all
select admno, 'tutionfee' col, tutionfee value
from tbl_stdfees

tbl_feecollection:

select sadmno, 'admissionfee' col, admissionfee value
from tbl_feecollection
union all
select sadmno, 'splfee' col, splfee value
from tbl_feecollection
union all
select sadmno, 'tutionfee' col, tutionfee value
from tbl_feecollection

Once you have unpivoted the data, you can easily get the total fees for each admno and the total fees paid. The entire query to get the result would be:

select tsf.admno, tsf.admissionfee, tsf.splfee, tsf.tutionfee,
  tsf.computerfee, tsf.stationary, tsf.transport, tsf.hostelfee,
  tsf.lab, tsf.library, tsf.miscell,
  fc.feepaid,
  tf.Totalfees - fc.feepaid OustandingFees
from tbl_stdfees tsf
inner join tbl_Admission ta
  on tsf.admno = ta.admno
inner join
(
  select admno, sum(value) TotalFees
  from
  (
    select admno, 'admissionfee' col, admissionfee value
    from tbl_stdfees
    union all
    select admno, 'splfee' col, splfee value
    from tbl_stdfees
    union all
    select admno, 'tutionfee' col, tutionfee value
    from tbl_stdfees
    union all
    select admno, 'computerfee' col, computerfee value
    from tbl_stdfees
    union all
    select admno, 'stationary' col, stationary value
    from tbl_stdfees
    union all
    select admno, 'transport' col, transport value
    from tbl_stdfees
    union all
    select admno, 'hostelfee' col, hostelfee value
    from tbl_stdfees
    union all
    select admno, 'lab' col, lab value
    from tbl_stdfees
    union all
    select admno, 'library' col, library value
    from tbl_stdfees
    union all
    select admno, 'miscell' col, miscell value
    from tbl_stdfees
  ) sf
  group by admno
) tf 
  on tsf.admno = tf.admno
left join
(
  select sadmno, sum(value) feepaid
  from
  (
    select sadmno, 'admissionfee' col, admissionfee value
    from tbl_feecollection
    union all
    select sadmno, 'splfee' col, splfee value
    from tbl_feecollection
    union all
    select sadmno, 'tutionfee' col, tutionfee value
    from tbl_feecollection
    union all
    select sadmno, 'computerfee' col, computerfee value
    from tbl_feecollection
    union all
    select sadmno, 'stationary' col, stationary value
    from tbl_feecollection
    union all
    select sadmno, 'transport' col, transport value
    from tbl_feecollection
    union all
    select sadmno, 'hostelfee' col, hostelfee value
    from tbl_feecollection
    union all
    select sadmno, 'lab' col, lab value
    from tbl_feecollection
    union all
    select sadmno, 'library' col, library value
    from tbl_feecollection
    union all
    select sadmno, 'miscell' col, miscell value
    from tbl_feecollection
  ) fe
  group by sadmno
) fc
  on ta.admno = fc.sadmno;

See SQL Fiddle with Demo.

As I said I would suggest that you normalize the tables in your database. I would suggest making the following changes to your database.

Create a payment table:

CREATE TABLE `tbl_payment` (
  `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,
  PRIMARY KEY  (`auto_id`)
);

Create a table to join the standard fees to each admno:

create table tbl_stdfee_admin
(
  `id` int(11),
  `Admno` varchar(50) default NULL,
  `fee_id` int(11) NOT NULL,
  `std_fee` float(10,2) default '0.00'
);

Then create a table to join the admno to each payment and fee collected:

create table tbl_collfee_admin
(
  `id` int(11),
  `payment_id` int(11),
  `Admno` varchar(50) default NULL,
  `fee_id` int(11) NOT NULL,
  `coll_fee` float(10,2) default '0.00'
);

If you normalize the tables, then you would use a query similar to the following:

select tsf.admno, 
  max(case when f.feename = 'Admission Fee' then tsf.std_fee else 0 end) AdmissionFee,
  max(case when f.feename = 'Spl Fee' then tsf.std_fee else 0  end) SplFee,
  max(case when f.feename = 'Tution Fee' then tsf.std_fee else 0  end) TutionFee,
  max(case when f.feename = 'Computer Fee' then tsf.std_fee else 0  end) ComputerFee,
  max(case when f.feename = 'Stationary' then tsf.std_fee else 0  end) Stationary,
  max(case when f.feename = 'Transport' then tsf.std_fee else 0  end) Transport,
  max(case when f.feename = 'Hostel Fee' then tsf.std_fee else 0  end) HostelFee,
  max(case when f.feename = 'Lab' then tsf.std_fee else 0  end) Lab,
  max(case when f.feename = 'Library' then tsf.std_fee else 0  end) Library,
  max(case when f.feename = 'Miscell' then tsf.std_fee else 0  end) Miscell,
  fc.FeesPaid,
  tf.TotalFees - fc.FeesPaid OustandingFees
from tbl_feevars f
left join tbl_stdfee_admin tsf
  on f.id = tsf.fee_id
left join tbl_Admission ta
  on tsf.admno = ta.admno
left join
(
  select admno, sum(std_fee) TotalFees
  from tbl_stdfee_admin
  group by admno
) tf
  on tsf.admno = tf.admno
left join
(
  select admno, sum(coll_fee) FeesPaid
  from tbl_collfee_admin
  group by admno
) fc
  on ta.admno = fc.admno
where tsf.admno is not null
group by tsf.admno, fc.FeesPaid, tf.TotalFees;

See SQL Fiddle with Demo. The second version could then be converted into a dynamic SQL version to get a list of all of the fees that you have in the tbl_feevars tables. Both versions will give a result:

| ADMNO | ADMISSIONFEE | SPLFEE | TUTIONFEE | COMPUTERFEE | STATIONARY | TRANSPORT | HOSTELFEE | LAB | LIBRARY | MISCELL | FEEPAID | OUSTANDINGFEES |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|     1 |         6000 |      0 |         0 |        2500 |       1500 |         0 |         0 |   0 |       0 |    3000 |    1400 |          11600 |
|     2 |         5000 |      0 |         0 |        2500 |       1500 |         0 |         0 |   0 |       0 |    5000 |    3000 |          11000 |

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – find and insert row to another table using thesql trigger

Related Question