I required Tsql query to show total values from detail lines against the summary lines on top of the each group

Question

I have following rows in table, I want TSQL Query to get sum of all detail rows in front of top summary row with "<>" sign. and so on, each group of lines has one summary row with item code starting/ending "<>" and followed by detailed rows until next summary row appears.

Answer 1

Hi Thanks everyone appreciate your answers, I apologize I missed the part of out in my question. Please see image I need detail rows also in out.

Answer 2

Hi @Ejaz Maqbool ,

I made some modifications on Jingyang's query and please refer below:

declare @mytable as table ( [COUNTER] int, ITEM_CONTENT int, ITEM varchar(20), UNIT varchar(100), QTY int, [DAY] int, Amount int )  
      
 insert into @mytable values  
 ( 1226548, 100, '<MIC>', 'Mobilization, Installation and Commissioning inclusive of:', 1, NULL, NULL ),  
 ( 1226549, 101, 'MBDMB', 'Mobilization/Demobilization',  1, 30000, 30000 ),   
 ( 1226550, 102, 'INSCM', 'Dewatering Works for Construction of Shugaiq 3-IWP — Seawater Intake Area',  1, 60000, 60000 ),   
 ( 1226551, 103, 'CSPVC', 'Supply of Temporary uPVC Casings for Well Installation',  1, 20000, 20000 ),   
 ( 1226552, 104, 'AGRGT', 'Supplyof 3/8" Aggregates for Well Installation',  1, 8000, 8000 ),   
 ( 1226553, 105, 'INSCM', 'Installation of Discharge Network to Discharge Point (max.125m)',  1, 23000, 23000 ),   
 ( 1226554, 200, '<SHM>', 'System Hire Charge inclusive of:',  1, NULL, NULL ),  
 ( 1226555, 201, 'HMDWS', 'Hire of Deepwell Dewatering System',  1, 1080, 1080 ),   
 ( 1226556, 202, 'HMDLN', 'Hire of Discharge & Electrical Network Infrastructures',  1, 270, 270 ),   
 ( 1226557, 203, 'HMGEN', '24 Hr Maintenance Crew Available on Site',  1, 300, 300 ),   
 ( 1226558, 204, 'PRSNL', 'Hire of Personnel',  1, 350, 350 )  
      
  
 Select [COUNTER], ITEM_CONTENT,ITEM,UNIT,  
QTY,[DAY],[Amount]  
 from   
  (select [COUNTER], ITEM_CONTENT,ITEM,UNIT,    
      Case when ITEM like '<%' then avg(QTY) over(partition by left(ITEM_CONTENT,2)) else QTY end QTY,   
      Case when ITEM like '<%' then sum([DAY])over(partition by left(ITEM_CONTENT,2))  else [DAY] end as [DAY],   
      Case when ITEM like '<%' then sum(Amount) over(partition by left(ITEM_CONTENT,2))else Amount end as Amount    
   from @mytable  
   ) t  
   where ITEM like '<%'  
union  
 Select [COUNTER], ITEM_CONTENT,ITEM,UNIT,  
null QTY,null [DAY],null [Amount] from @mytable  
where ITEM not like '<%'  
order by COUNTER  

Output：

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.