Compute the total amount of fee received by school

Question

I am using phpMYAdmin, and I fail to compute the total amount of fee received by school.

FeeTable

Status     FeeReceived
Yes         100
No          200

RegTable

Status     School
Yes        A
Yes        B
No         C
No         C
No         C
No         D
No         E
No         E
No         E
Yes        A
Yes        A
Yes        F
Yes        F
Yes        F
Yes        F
No         G
Yes        H
Yes        H
No         I
No         J
No         K
No         L

I can only compute the total fee for either Status=Yes or Status=No.

For FeeTable, If person's Status is Yes, s/he is charged $100. Otherwise, s/he is charged $200. RegTable records the Status and School of a person. I want to compute the total fee received for each school. => $300 from School A $100 from School B $600 from School C $200 from School D ..... ..... ..... ..... and so on. @MicheleLaFerla — Fiona, Nov 30 '14 at 14:43

Fred · Accepted Answer · 2014-11-30T17:16:36.287

-1

If you want a total for yes and a total for no then

SELECT Sum(f.FeeReceived) As FeeReceived, r.Status FROM RegTable r JOIN FeeTable f ON f.Status = r.Status GROUP BY r.Status

If you want it by status in school:

SELECT Sum(f.FeeReceived) As FeeReceived, r.Status, r.School FROM RegTable r JOIN FeeTable f ON f.Status = r.Status GROUP BY r.Status , r.School

Total by school:

  SELECT Sum(f.FeeReceived) As FeeReceived, r.School FROM RegTable r JOIN FeeTable f ON f.Status = r.Status GROUP BY  r.School

edited Nov 30 '14 at 17:16

answered Nov 30 '14 at 09:55

Fred

5,663
4
45
74

Thanks for your answering! My question may be unclear, lets me explain the scenario. For FeeTable, If person's Status is Yes, s/he is charged $100. Otherwise, s/he is charged $200. RegTable records the Status and School of a person. I want to compute the total fee received for each school. => $300 from School A $100 from School B $600 from School C $200 from School D ..... ..... ..... ..... and so on. @Fred Thanks for your help again! – Fiona Nov 30 '14 at 14:50
@Fiona try the third one ive just added – Fred Nov 30 '14 at 17:17
Can you tell me what are the meaning of "f" and "r"? Thanks! @Fred – Fiona Nov 30 '14 at 18:07
Yes, they are just an alias for the table name. So if you don't alias the table like this `RegTable r` in the query you put `RegTable.Status`. `RegTable r` is like giving it a nickname so you can reference it without typing the fullname. If a field in your query appears in more than one table you must tell SQL which table you want to take the data from. Always typing `Tablename.FieldName` is a good habit to get into. – Fred Nov 30 '14 at 19:39
Reason for the downvote? People should really be forced to give a reason. A simple downvote is no good to anyone! – Fred Nov 30 '14 at 19:42
I solve the problem! the code is SELECT School,SUM(`FeeReceived`) FROM `Fee`,`Reg` WHERE `Fee`.`Status`=`Reg`.`Status` GROUP BY School. By the way, I didn't not give a down vote. @Fred – Fiona Dec 01 '14 at 03:01
@Fred: downvote is because you answered a bad question that showed no effort put in. I don't believe in encouraging bad questions with answers. – siride Dec 01 '14 at 04:36
@Fiona that is the same code as my last answer except: You have used the older join syntax which you should try to come away from IMO (See http://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax) and you have not aliased the tables. Wither way they produce the same results, glad you got there. – Fred Dec 01 '14 at 07:45
@siride fair enough but the guide says "while voting down a post signals the opposite: that the post contains wrong information, is poorly researched, or fails to communicate information." Nowhere does it say "because you don't agree with people answering bad questions" maybe your downvote is misplaced. – Fred Dec 01 '14 at 07:52

Compute the total amount of fee received by school

1 Answers1