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
Looking at your table structures, I would suggest that you change the design to something that is normalized. For example:
Using something similar to above will allow you to add new subjects without having to alter your table. Then you will just join the tables on the
sub_id
to get the list of subjects for each class.See SQL Fiddle with Demo. The above will give you a result in rows, but you could easily apply an aggregate function with a CASE expression to pivot the data into columns. Similar to the following:
See Demo.
But if you don't change your current table structure and if you don't know the columns that you are going to return, then you will have to implement a prepared statement to generate dynamic SQL.
First, you will create the list of the classes:
See Demo. This will give you the list of classes for each
c_id
. Once you have the list of classes, then you can add this to the rest of the sql string so the full code will be:See SQL Fiddle with Demo