December 25, 2015 at 4:28 am
I've table as follow,
CREATE TABLE [dbo].[tmpaccthist_2013_2014](
[cmpy_code] [varchar](50) NULL,
[acct_code] [varchar](50) NULL,
[year_num] [varchar](50) NULL,
[period_num] [varchar](50) NULL,
[open_amt] [varchar](50) NULL,
[debit_amt] [varchar](50) NULL,
[credit_amt] [varchar](50) NULL,
[close_amt] [varchar](50) NULL,
[stats_qty] [varchar](50) NULL,
[pre_close_amt] [varchar](50) NULL,
[budg1_amt] [varchar](50) NULL,
[budg2_amt] [varchar](50) NULL,
[budg3_amt] [varchar](50) NULL,
[budg4_amt] [varchar](50) NULL,
[budg5_amt] [varchar](50) NULL,
[budg6_amt] [varchar](50) NULL,
[ytd_pre_close_amt] [varchar](50) NULL,
[hist_flag] [varchar](50) NULL,
[ytd_budg1_amt] [varchar](50) NULL,
[ytd_budg2_amt] [varchar](50) NULL,
[ytd_budg3_amt] [varchar](50) NULL,
[ytd_budg4_amt] [varchar](50) NULL,
[ytd_budg5_amt] [varchar](50) NULL,
[ytd_budg6_amt] [varchar](50) NULL,
CONSTRAINT [tmpaccthist_2013_2014_UQ1] UNIQUE NONCLUSTERED
(
[cmpy_code] ASC,
[acct_code] ASC,
[year_num] ASC,
[period_num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
My data as attachment - result.png
My distinct cmpy_code as follow,
cmpy_code
-------------------------
AA
AAM1
AAM2
AAN1
AAN2
SA
SAM1
SAM2
SAN1
SAN2
I want to do SQL Grouping. My expected result as follow,
cmpy_code | year_num | open_amt
------------------------------------------
SA | 2013 | 5889888998.00
SA | 2014 | 6554383888.00
AA | 2013 | 7767766667.00
AA | 2014 | 5577997778.00
SAM1 | 2013 | 5567476655.76
SAM1 | 2014 | 4477756666.76
.....
.....
.....
.....
.....
Please help
December 26, 2015 at 12:25 pm
So far so good with the DDL but you are missing the sample data matching the expected results.
😎
December 26, 2015 at 5:45 pm
Eirikur Eiriksson (12/26/2015)
So far so good with the DDL but you are missing the sample data matching the expected results.😎
Owh Sorry. Will do the correction
December 26, 2015 at 6:36 pm
From what I infer, you're looking for something like this:
SELECT cmpy_code, year_num, SUM(open_amt)
FROM tmpaccthist_2013_2014
GROUP BY cmpy_code, year_num;
From your sample output, I don't see the ORDER BY clause.
However, the real problem I see is with your table design. The open_amt (and all other amounts and quantities) are varchar(50). This means you can have any alphanumeric data in these columns, so any mathematical operations on them are going to spell trouble. My advice would be to pick the appropriate numeric data types. Just make sure you allow enough space and precision to hold the data that's appropriate to each column.
December 26, 2015 at 7:18 pm
Hello,
This is the table design, and set of data
CREATE TABLE [dbo].[tmpaccthist_2013_2014_Test2](
[cmpy_code] [nvarchar](50) NULL,
[acct_code] [nvarchar](50) NULL,
[chart_code] [nvarchar](5) NULL,
[pusat_kos] [nvarchar](10) NULL,
[year_num] [int] NULL,
[period_num] [int] NULL,
[open_amt] [decimal](18, 2) NULL,
CONSTRAINT [tmpaccthist_2013_2014_Test2_UQ1] UNIQUE NONCLUSTERED
(
[cmpy_code] ASC,
[acct_code] ASC,
[year_num] ASC,
[period_num] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[tmpaccthist_2013_2014_Test2] ([cmpy_code], [acct_code], [chart_code], [pusat_kos], [year_num], [period_num], [open_amt]) VALUES (N'AA', N'02-11101-201501', N'11101', N'201501', 2013, 1, CAST(0.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpaccthist_2013_2014_Test2] ([cmpy_code], [acct_code], [chart_code], [pusat_kos], [year_num], [period_num], [open_amt]) VALUES (N'AA', N'02-11101-201501', N'11101', N'201501', 2013, 2, CAST(10.50 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpaccthist_2013_2014_Test2] ([cmpy_code], [acct_code], [chart_code], [pusat_kos], [year_num], [period_num], [open_amt]) VALUES (N'AA', N'02-11101-201501', N'11101', N'201501', 2013, 3, CAST(4.50 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpaccthist_2013_2014_Test2] ([cmpy_code], [acct_code], [chart_code], [pusat_kos], [year_num], [period_num], [open_amt]) VALUES (N'SA', N'01-93115-0130110-1', N'93115', N'0130110', 2013, 1, CAST(79.80 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpaccthist_2013_2014_Test2] ([cmpy_code], [acct_code], [chart_code], [pusat_kos], [year_num], [period_num], [open_amt]) VALUES (N'AA', N'02-11101-201501', N'11101', N'201501', 2013, 4, CAST(34.50 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpaccthist_2013_2014_Test2] ([cmpy_code], [acct_code], [chart_code], [pusat_kos], [year_num], [period_num], [open_amt]) VALUES (N'SA', N'01-93115-0130110-1', N'93115', N'0130110', 2013, 2, CAST(11.20 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpaccthist_2013_2014_Test2] ([cmpy_code], [acct_code], [chart_code], [pusat_kos], [year_num], [period_num], [open_amt]) VALUES (N'AA', N'02-11101-201501', N'11101', N'201501', 2014, 1, CAST(240.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpaccthist_2013_2014_Test2] ([cmpy_code], [acct_code], [chart_code], [pusat_kos], [year_num], [period_num], [open_amt]) VALUES (N'AA', N'01-93115-0130110-1', N'93115', N'0130110', 2014, 1, CAST(34.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpaccthist_2013_2014_Test2] ([cmpy_code], [acct_code], [chart_code], [pusat_kos], [year_num], [period_num], [open_amt]) VALUES (N'AAN2', N'02-11101-201501', N'11101', N'201501', 2013, 1, CAST(560.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[tmpaccthist_2013_2014_Test2] ([cmpy_code], [acct_code], [chart_code], [pusat_kos], [year_num], [period_num], [open_amt]) VALUES (N'SA', N'01-29314-0125001-3', N'29314', N'0125001', 2014, 3, CAST(400.00 AS Decimal(18, 2)))
GO
Expected Result
----------------------------------------------------------------------
AA | 01-93115-0130110-1 | 93115 | 0130110 | 2014 | 34.00
AA | 02-11101-201501 | 11101 | 201501 | 2013 | 49.50
AA | 02-11101-201501 | 11101 | 201501 | 2014 | 240.00
AAN2 | 02-11101-201501 | 11101 | 201501 | 2013 | 560.00
SA | 01-29314-0125001-3 | 29314 | 0125001 | 2014 | 400.00
SA | 01-93115-0130110-1 | 93115 | 0130110 | 2013 | 91.00
December 26, 2015 at 7:38 pm
It seems that's a pretty straightforward query, where you just want the sum of one column, grouped by another set of columns, and the desired result set is just the sum and the columns used for grouping. For that all you need is SUM and a GROUP BY with the other columns you're returning.
Something like this:
SELECT cmpy_code, acct_code, chart_code, pusat_kos, year_num, SUM(open_amt)
FROM tmpaccthist_2013_2014_Test2
GROUP BY cmpy_code, acct_code, chart_code, pusat_kos, year_num
ORDER BY cmpy_code, acct_code, chart_code, year_num
Cheers!
December 26, 2015 at 7:44 pm
Owh. Ok. Just want to audit my SQL with yours.
Thank you Sir
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply