SQL query to get subtotal on the last row of bill number

I have written the following SQL query which gives the result not in a way I desire. The only change I want is that, I don’t want to repeat the subtotal on every single line, which is what my query is doing now:

Instead, I want it only on the last row of every unique bill_number:

The query is

    SELECT 
  ROW_NUMBER() OVER(
    ORDER BY 
      S.bill_num
  ) SN, 
  ROW_NUMBER() OVER(
    PARTITION BY S.bill_num 
    ORDER BY 
      S.bill_num
  ) AS [ITEM_NO.], 
  s.bill_date AS [BILL_DATE], 
  s.bill_num AS [BILL_NO.], 
  s.custid AS [CUST_ID], 
  c.fullname [CUSTOMER], 
  s.article AS [ARTICLE], 
  s.price AS [MRP], 
  s.linetotal AS [TOTAL],
  b.billamt as [BILL_TOTAL] 
FROM 
  sold s 
  LEFT JOIN customers c ON s.custid = c.custid 
  LEFT JOIN bills b ON S.bill_num = B.bill_num  
WHERE 
  s.bill_date = CONVERT(date, '2020/12/01') AND B.isexpress <> 'P'  
ORDER by 
  S.bill_num

I guess you get the idea of what I want from the images given

Answer

You can do this with row_number() and a CASE expression.

However, your query and result suggest that you have multiple rows sharing the same s.bill_num; to get a stable result, you need a column that can be used to unambigously order rows having duplicate values – let me assume s.id:

SELECT 
    ROW_NUMBER() OVER(ORDER BY S.bill_num, S.id) SN, 
    ROW_NUMBER() OVER(PARTITION BY S.bill_num ORDER BY S.id) AS [ITEM_NO.], 
    s.bill_date AS [BILL_DATE], 
    s.bill_num AS [BILL_NO.], 
    s.custid AS [CUST_ID], 
    c.fullname [CUSTOMER], 
    s.article AS [ARTICLE], 
    s.price AS [MRP], 
    s.linetotal AS [TOTAL],
    CASE WHEN ROW_NUMBER() OVER(PARTITION BY S.bill_num ORDER BY S.id DESC) = 1 THEN b.billamt END as [BILL_TOTAL] 
FROM sold s 
LEFT JOIN customers c ON s.custid = c.custid 
LEFT JOIN bills b ON S.bill_num = B.bill_num AND B.isexpress <> 'P'  
WHERE s.bill_date = CONVERT(date, '2020/12/01')
ORDER by S.bill_num

Note that I moved the condition on B.isexpress from the WHERE clause to the ON clause of the relevant LEFT JOIN; otherwise, this would filter out rows that have no match in bills – which does not seem to be your intent.