0

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;
  • 2
    You are trying to use a column list as the argument (list) of SUM() (twice), but SUM accepts only one argument (column or expression), not a list. Because of the incorrect use of SUM(), it is not entirely clear what you are trying to achieve. An example of the expected output (output resultset) might help. Also, please consider putting the relevant parts of your code and sample data into your question. I like SQL Fiddle very much but that doesn't prevent it from falling sometimes. Just imagine it stops working at this very minute, what then? Your question will stop making much sense. – Andriy M Aug 1 '13 at 7:39
  • thank you @AndriyM for your response. I thought if i post my entire question with code, the post will be too lengthy and dba's will never like to read my post. thats y i have posted my question only, and linked my db & query from sql fiddle in my question – Narendar_CH Aug 1 '13 at 7:50
  • Actually i want output as like this sqlfiddle.com/#!2/a26c7/21 – Narendar_CH Aug 1 '13 at 7:52
  • 1
    You don't have (nor need) to put all the code into your question. Put only most relevant parts that need to be shown exactly and explain the rest in simple terms, leaving the Fiddles only as (helpful) illustration. Same for the sample data, which you don't need to present as DDL/DML in the question. Talking of the data, it would also be a good idea to simplify your problem in terms of tables and columns involved so that you could easily show in your question what you are trying to do and not fear that the length of your question might drive away potential answerers. – Andriy M Aug 1 '13 at 8:20
  • 2
    Have you tried looking at the resulting query that you are executing eventually? I'm still not sure what the calculations must be but I suspect your last attempt is not bringing your the results you were hoping for. Just look at the query. It seems like you are trying to build a query without having a firm idea what it should look like. I suggest you first try writing a complete static query that does what you want. Basing off the static query, it will be easier for you to figure out how to construct it dynamically. – Andriy M Aug 1 '13 at 13:17
5

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 |
5

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> 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> 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> 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.

  • Thank you. Here you are mentioning Column names in select statement so it produces the output, But i don't know how many fee types and fixed amounts for that fee type. Rather than mentioning Column names manually can't we produce the Output.?? – Narendar_CH Aug 2 '13 at 5:42
  • 1
    I printed the explicit column names, to show what the query actually does. Now, you can go and replace the column names with variables. My main point was, that "SUM" was giving you the error message, and that you need to use "+" instead, if keeping the same table structure. A better way is normalization. Let me know if you need an explicit example. – Ursula Aug 2 '13 at 6:23
  • I will be very happy if u give me such example – Narendar_CH Aug 2 '13 at 8:51
  • 1
    Added an example in tutorial style - step by step. I didn't include a final query that puts all the results in the end in one line - I leave that to you, @bluefoot has a lot of pointers to that in her or his answer. – Ursula Aug 2 '13 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.