How to find the sum from value of sql column-CodePudding

I have two tables with following data.

studenttbl:

seatno  sname   coursename      scoursefee
------------------------------------------
1006    Vivek    MS-CIT           4500
1005    RAJESH   TALLY ERP        5200
1004    Anil     MS-CIT           4500
1003    ANKITA   OFFICE EXPERT    4200
1002    SACHIN   TALLY ERP        5200
1001    VIJAY    MS-CIT           4500

feetbl:

feeid   seatno  receivedamt receiptno   receiveddate
-----------------------------------------------------------
  1     1001        2500     1001   2021-10-02 06:27:28.000
  2     1002        2200     1002   2021-10-02 06:28:11.000
  3     1003        2700     1003   2021-10-02 06:29:03.000
  4     1003        500      1004   2021-10-02 06:32:20.000
  5     1004        1500     102    2021-10-02 07:02:37.000
  6     1004        1000     101    2021-10-02 07:08:03.000
  7     1005        5200     103    2021-10-02 07:23:46.000

I need to find below data, but I am not getting balance value of seatno 1006:

jdate      seatno   sname   coursename  Scoursefee  paid    balance
-------------------------------------------------------------------
02 Oct 2021 1005    RAJESH  TALLY ERP       5200    5200    0
02 Oct 2021 1004    Anil    MS-CIT          4500    2500    2000
02 Oct 2021 1003    ANKITA  OFFICE EXPERT   4200    3200    1000
02 Oct 2021 1002    SACHIN  TALLY ERP       5200    2200    3000
02 Oct 2021 1001    VIJAY   MS-CIT          4500    2500    2000

I am using this SQL query:

SELECT 
    CONVERT(VARCHAR, jdate, 106) AS jdate,
    st.seatno,
    sname,
    coursename,
    Scoursefee,
    SUM(ft.receivedamt) AS paid,
    st.Scoursefee - SUM(ft.receivedamt) AS balance
FROM 
    studenttbl st
INNER JOIN 
    feetbl ft ON st.seatno = ft.seatno
WHERE 
    JDate BETWEEN '1990-01-01 00:00:00'
              AND '2021-10-05 00:00:00'
GROUP BY 
    st.seatno, st.Scoursefee, sname, 
    jdate, coursename, Scoursefee
ORDER BY 
    st.seatno DESC

Please help me with this.

CodePudding user response：

This issue is due to INNER JOIN versus LEFT JOIN. When you INNER JOIN youll only get matches between two tables. When you LEFT JOIN, you will get all values from the left table and only matches form the right side.

Also, with this issue, your SUM function will break. You need to wrap the LEFT JOIN'd table's column in ISNULL(col,0) so youre aggregates work correctly.

I also changed your date comparison in the WHERE clause to use <= and >=. Aaron Bertrandt goes over this topic a few times, here is a good link:

https://www.mssqltips.com/sqlservertutorial/9316/sql-server-between-dates-issue/

SELECT convert(VARCHAR, jdate, 106) AS jdate
    ,st.seatno
    ,sname
    ,coursename
    ,Scoursefee
    ,sum(ft.receivedamt) AS paid
    ,st.Scoursefee - sum(ISNULL(ft.receivedamt,0)) AS balance
FROM studenttbl st
LEFT JOIN feetbl ft ON st.seatno = ft.seatno
WHERE JDate >='1990-01-01 00:00:00'
        AND JDate <='2021-10-05 00:00:00'
GROUP BY st.seatno
    ,st.Scoursefee
    ,sname
    ,jdate
    ,coursename
    ,Scoursefee
ORDER BY st.seatno DESC

CodePudding user response：

It's because Seat 1006 does not show up in the feetbl table. Try doing a left join on it so that you pull all info from Studenttbl. Or, add the appropriate data to the feetbl.