Need help with Group

Transact-SQL
1

Hello,

I've table design as follow,

CREATE TABLE [dbo].[crpt_ImbanganDuga_4_Vertical](
    [batch_Id] [uniqueidentifier] NULL,
    [kod] [nvarchar](5) NULL,
    [chart_code] [nvarchar](5) NULL,
    [desc_text] [nvarchar](50) NULL,
    [group_1Digit] [nvarchar](5) NULL,
    [group_2Digit] [nvarchar](5) NULL,
    [group_3Digit] [nvarchar](5) NULL,
    [amt] [decimal](18, 2) NULL
) ON [PRIMARY]

My data as follow (in Print Screen),

H-1.png798×410 10.2 KB

My question is, how my SQL looks like to Grouping By chart_code, and sum (amt)?

Please help

2

How do you want the result to looks like ?

3

Should be like this

batch_Id | kod   | chart_code  | desc_text     | group_1Digit   | group_2Digit   | group_3Digit   | amt
----------------------------------------------------------------------------------------------------------------
F8321864-1925-44C2-93DC-4244F62EFA9C    SA    11101    Kakitangan Biasa    10000    11000    11100    sum(amt)
F8321864-1925-44C2-93DC-4244F62EFA9C    SA    11102    Kakitangan HRM      10000    11000    11100    sum(amt)

chart_code not repeated

4 
SELECT batch_Id , kod   , chart_code  , desc_text   ,  group_1Digit   ,  group_2Digit   , group_3Digit   , SUM(amt)
FROM    yourtable
GROUP BY  batch_Id , kod   , chart_code  , desc_text   ,  group_1Digit   ,  group_2Digit   , group_3Digit
5 
SELECT batch_Id , kod   , chart_code  , desc_text   ,  group_1Digit   ,  group_2Digit   , group_3Digit   , SUM(amt)
from dbo.crpt_ImbanganDuga_4_Vertical
where batch_Id='f8321864-1925-44c2-93dc-4244f62efa9c'
GROUP BY  batch_Id , kod   , chart_code  , desc_text   ,  group_1Digit   ,  group_2Digit   , group_3Digit
order by chart_code

chart_code still repeated

6

you have diff chart code for the same batch_id. Which one do you want in the result ?

7

I want - chart_code

Did I need to produce unique key for each row?

8

you didn't really answer my question.

anyway, this will give you one chart code only per batch_id. The minimum value of the chart code

SELECT batch_Id , kod   , MIN(chart_code)  , desc_text   ,  group_1Digit   ,  group_2Digit   , group_3Digit   , SUM(amt)
from dbo.crpt_ImbanganDuga_4_Vertical
where batch_Id='f8321864-1925-44c2-93dc-4244f62efa9c'
GROUP BY  batch_Id , kod   , desc_text   ,  group_1Digit   ,  group_2Digit   , group_3Digit
order by chart_code