SQLTeam.com | Weblogs | Forums

Need help with Group


#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),

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