Use of Group by and aggregate function on given table

Question

I have a below table ContainerNumber Orgzn PO_Gross ------------------------------------------------------------------------------------------ MNBU3319850 [AZ]Komati Fruits (Pty) Ltd 40 SZLU9006949 [ZL]Zestfruit (Pty) Ltd 30 MNBU3319850 [AZ]Komati Fruits (Pty) Ltd 10 SZLU9006949 [ZL]Zestfruit (Pty) Ltd 50 TTNU8324679 [ZL]Zestfruit (Pty) Ltd 70 -------------------------------------------------------------------------------------------- Need Output in below format. -------------------------------------------------------------------------------------------- ContainerNumber Orgzn PO_Gross ------------------------------------------------------------------------------------------- MNBU3319850 [AZ]Komati Fruits (Pty) Ltd 50 SZLU9006949 [ZL]Zestfruit (Pty) Ltd 80 TTNU8324679 [ZL]Zestfruit (Pty) Ltd 70

Jignesh Kumar · Answer

Hi Abhijit, Please use this one, CREATE TABLE [dbo].[Container]( [ContainerNumber] [ varchar ](50) NULL , [Orgzn] [ varchar ](200) NULL , [PO_Gross] [ int ] NULL ) ON [ PRIMARY ] GO INSERT [dbo].[Container] ([ContainerNumber], [Orgzn], [PO_Gross]) VALUES (N 'MNBU3319850' , N '[AZ]Komati Fruits (Pty) Ltd' , 40) INSERT [dbo].[Container] ([ContainerNumber], [Orgzn], [PO_Gross]) VALUES (N 'SZLU9006949' , N '[ZL]Zestfruit (Pty) Ltd' , 30) INSERT [dbo].[Container] ([ContainerNumber], [Orgzn], [PO_Gross]) VALUES (N 'MNBU3319850' , N '[AZ]Komati Fruits (Pty) Ltd' , 10) INSERT [dbo].[Container] ([ContainerNumber], [Orgzn], [PO_Gross]) VALUES (N 'SZLU9006949' , N '[ZL]Zestfruit (Pty) Ltd' , 50) INSERT [dbo].[Container] ([ContainerNumber], [Orgzn], [PO_Gross]) VALUES (N 'TTNU8324679' , N '[ZL]Zestfruit (Pty) Ltd' , 70) Select Query Select ContainerNumber, Orgzn, Sum (Po_Gross) as Po_Gross from Container group by ContainerNumber, Orgzn

Sachin Singh · Answer

Select ContainerNumber,Max(Orgzn) as Orgzn, Sum(Po_Gross) as Po_Gross from table group by ContainerNumber