CODEWITHSUNDEEP
sqlsql-serverjoingroup-by
Maulik Shah
Maulik Shah

Reputation: 673

Select data from three table in sql

I have three table Like

Student : Sid, SName, SEmail

Fees_Type : Fid, FName, FPrice

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

Data of Each Table :

Student : 
SID |SName | SEmail      
1   | ABC  | [email protected]
2   | XYZ  | [email protected]  

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 data Like 

SID|SName|SEmail      | Total_Chess_Played|Total_Cricket_Played | ToTal_Fees
1  | ABC |[email protected] |      2            |         1           |  400
2  | XYZ |[email protected] |      1            |         1           |  300

I have tried these following query but can not get Group by 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 in SQL. So Please Help Me.

Thank You.

Upvotes: 6

Views: 74

Answers (2)

ThePravinDeshmukh
ThePravinDeshmukh

Reputation: 1913

try this

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

Upvotes: 1

Arion
Arion

Reputation: 31239

You could 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

Upvotes: 3

Related Questions