2

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. plz look **@ mysql query 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.

Narendar_Dotnet
  • 153
  • 4

1 Answers1

1

What is Your desired output ?

I have made that working in this didle http://www.sqlfiddle.com/#!2/a26c7/15

i Think, that @ is not good... moreover, You want to use arrays... there's no such thing as arrays in MySQL You have to use LOOP to simulate array or TMP TABLE. See this question with good answer 4u:

How can I simulate an array variable in MySQL?

Community
  • 1
  • 1
jaczes
  • 1,366
  • 2
  • 8
  • 16
  • thank you @jaczes for your early response, But my expected output is display each Fee type, Fixed amount for Fee type, Paid amount for Fee type and Balance amount to be paid – Narendar_Dotnet Aug 01 '13 at 07:24
  • see http://www.sqlfiddle.com/#!2/a26c7/16 there You do have content of `@sqlList`, `@sqlList1` - probably You wil have to do that in loop – jaczes Aug 01 '13 at 07:25
  • You have to be more specyfic when You will set `@sqllist` – jaczes Aug 01 '13 at 07:27
  • after modified by you now getting result into @sqlLsit=tsf.AdmissionFee,tsf.SplFee,tsf.TutionFee,tsf.ComputerFee,tsf.Stationary,tsf.Transport,tsf.Hostelfee,tsf.Lab,tsf.Library,tsf.Miscell – Narendar_Dotnet Aug 01 '13 at 07:31
  • and also for @sqlList1=tsf.AdmissionFee,tsf.SplFee,tsf.TutionFee,tsf.ComputerFee,tsf.Stationary,tsf.Transport,tsf.Hostelfee,tsf.Lab,tsf.Library,tsf.Miscell – Narendar_Dotnet Aug 01 '13 at 07:31
  • but i want @sqlList1= tfc.AdmissionFee,tfc.SplFee and so-- – Narendar_Dotnet Aug 01 '13 at 07:32
  • Sorry i did'nt seen, @sqlList,@sqlList1 resulting correct output – Narendar_Dotnet Aug 01 '13 at 07:36
  • could you tell me how can i take the comma separated values into each variable, Is it possible in sql query? As I am generating the @sqlList= SUM(tfc.AdmissionFee),SUM(tfc.ComputerFee),SUM(tfc.Hostelfee),SUM(tfc.Lab),SUM(tfc.Library),SUM(tfc.Miscell),SUM(tfc.SplFee),SUM(tfc.Stationary),SUM(tfc.Transport),SUM(tfc.TutionFee) – Narendar_Dotnet Aug 01 '13 at 12:41
  • See this : http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_substring-index – jaczes Aug 01 '13 at 13:08
  • and this - my post http://stackoverflow.com/questions/17719170/mysql-how-to-separate-a-telephone-field-in-one-table-into-prefix-number-in-two/17719466#17719466 – jaczes Aug 01 '13 at 13:11