Select data from three tables in sql

Question

I have three tables like

Student : Sid, SName, SEmail

Fees_Type : Fid, FName, FPrice

StudentFees : Sid (FK from Student), Fid (FK from Fees_Type), FDate

Data for each table:

Student : SID |SName | SEmail 1 | ABC | [e-mail] 2 | XYZ | [e-mail] Fees_Type: Fid | FName | FPrice 1 | Chess | 100 2 | Cricket | 200 StudentFees: Sid | Fid| FDate 1 | 1 | 5/2 1 | 2 | 6/2 2 | 1 | 7/2 2 | 2 | 8/2 1 | 1 | 6/2 

Now I want to get Like data

 SID|SName|SEmail | Total_Chess_Played|Total_Cricket_Played | ToTal_Fees 1 | ABC | [e-mail] | 2 | 1 | 400 2 | XYZ | [e-mail] | 1 | 1 | 300 

I tried this next query but cannot get Group or perfect result

 SELECT s.sId, SEmail, SName, FName ,FPrice FROM Student s INNER JOIN StudentFees sf ON s.sId = sf.EId INNER JOIN Fees_Type f ON f.fId = sf.fId WHERE MONTH(pr.TDDate) = MONTH(dateadd(dd, 1, GetDate())) AND YEAR(pr.TDDate) = YEAR(dateadd(dd, -1, GetDate())) 

I am new to SQL. So please help me.

Thanks.

Answer 1

You can do something like this:

 SELECT Student.SID, Student.SName, Student.SEmail, SUM(CASE WHEN Fees_Type.FName='Chess' THEN 1 ELSE 0 END) AS Total_Chess_Played, SUM(CASE WHEN Fees_Type.FName='Cricket' THEN 1 ELSE 0 END) AS Total_Cricket_Played, SUM(Fees_Type.FPrice) AS ToTal_Fees FROM Student JOIN StudentFees ON Student.sId = StudentFees.EId JOIN Fees_Type ON Fees_Type.fId = StudentFees.fId WHERE MONTH(StudentFees.TDDate) = MONTH(dateadd(dd, 1, GetDate())) AND YEAR(StudentFees.TDDate) = YEAR(dateadd(dd, -1, GetDate())) GROUP BY Student.SID, Student.SName, Student.SEmail 

Answer 2

try it

 SELECT s.SID, s.SName, s.SEmail, SUM(CASE WHEN ft.FName='Chess' THEN 1 ELSE 0 END) AS Total_Chess_Played, SUM(CASE WHEN ft.FName='Cricket' THEN 1 ELSE 0 END) AS Total_Cricket_Played, SUM(ft.FPrice) AS ToTal_Fees FROM Student s JOIN StudentFees sf ON s.sId = sf.Sid JOIN Fees_Type ft ON ft.fId = sf.fId GROUP BY s.SID, s.SName, s.SEmail