Need to handle custom query summation for group

Question

I have this table name "TB1" contain of these columns :

 Bill.No     Patient_Name       Xray    Price    qty  Doctor
  
   1           Ronny             Test1    100     2   Admin
   1           Ronny             Test2    200     3   Admin
   2           Mina              Test1    100     2   User
   3           Roan              Test4    400     1   Admin
   4           Bedo              Test3    50      1   User
   4           Bedo              Test3    50      1   User
   5           Phill             Test3    50      4   Admin

I need a group with Bill.NO with sum column qty ,price from "TB1" to be like this :

Bill.No     Patient_Name       Xray    Price     qty Doctor
      
       1           Ronny        Test1    300     5   Admin
                                Test2         
       2           Mina         Test1    100     2   User
       3           Roan         Test4    400     1   Admin
       4           Bedo         Test3    100     2   User
                                Test3            
       5           Phill        Test3    50      4   Admin

What I tried to do , I wrote this query but not doing as I want :

Select Bill.No, Patient_Name, Xray, sum(Price) as Price, sum(qty) as qty, Doctor
From TB1
group by Bill.No, Patient_Name, Doctor, Xray

Yes i know it from report but I need it with sql it's a bit hard I think ! — mandarin software, Sep 01 '21 at 14:20

score 2 · Accepted Answer · answered Sep 01 '21 at 14:28

Really, you should leave these kind of things to the presentation layer.

But you could hack it with ROW_NUMBER

SELECT
  BillNo = CASE WHEN rn = 1 THEN No END,
  Patient_Name = CASE WHEN rn = 1 THEN Patient_Name END,
  Xray,
  Price = CASE WHEN rn = 1 THEN SumPrice END,
  qty = CASE WHEN rn = 1 THEN SumQty END,
  Doctor = CASE WHEN rn = 1 THEN Doctor END
FROM (
    SELECT *,
      rn = ROW_NUMBER() OVER (PARTITION BY Bill.No ORDER BY Xray),
      SumPrice = SUM(Price) OVER (PARTITION BY Bill.No),
      SumQty = SUM(qty) OVER (PARTITION BY Bill.No)
    FROM TB1
) TB1

Wow that's what I want and doing wonderful , thanks for saving my days :) — mandarin software, Sep 01 '21 at 14:34

score 1 · Answer 2 · answered Sep 01 '21 at 14:14

1

This is probably the closest you can come without some crazy gyrations:

Select 
    Bill.No, 
    Patient_Name, 
    STRING_AGG(Xray,'
') AS Xray, 
    sum(Price) as Price, 
    sum(qty) as qty, 
    Doctor
From TB1
group by Bill.No, Patient_Name, Doctor

That said, formatting games like you are trying to do with the Xray column are better handled by client apps.

answered Sep 01 '21 at 14:14

RBarryYoung

55,398
14
96
137

1

Not doing the trick as I mention but really that was amazing and a good news for me thanks many thanks to you . – mandarin software Sep 01 '21 at 14:38

score 1 · Answer 3 · answered Sep 01 '21 at 14:15

Maybe use FOR XML STUFF magic to do this. See the link for details How Stuff and 'For Xml Path' work in SQL Server?

   Select Bill.No, Patient_Name, STUFF((
        SELECT ', ' + [Xray]  
        FROM TB1 
        WHERE ([Bill.No]= EXT.[Bill.No]) 
        FOR XML PATH(''),TYPE).value('.','VARCHAR(MAX)')
      ,1,1,'') AS [Xray],
    sum(Price) as Price, sum(qty) as qty, Doctor
    FROM TB1 EXT
    GROUP BY Bill.No, Patient_Name, Doctor, Xray

Need to handle custom query summation for group

3 Answers3