October 24, 2016 at 6:24 am
Hi Gurus,
Just wanted to seek out for your help. This is a bit opposite of what aggregation should be. :hehe:
I wanted to aggregate records with different values and dont aggregate records with the same value.
Here's the code to create the test table
create table test1
(col1 int,
col2 int,
col3 varchar(2),
col4 decimal(3,1)
)
--Test Data
insert
into test1(col1,col2,col3,col4)
values (1,2,'A',5),
(1,2,'B',5),
(2,3,'B',6),
(2,3,'B',6.5),
(2,3,'B',6.3),
(3,4,'B',6),
(3,4,'B',6),
(3,4,'A',6),
(3,4,'B',6),
(4,5,'C',12),
(4,5,'D',11),
(5,6,'C',4.5),
The result I wanted is:
Col1 Col2 Col3 Col4
1 2 A 10
2 3 B 6
2 3 B 6.5
2 3 B 6.3
3 4 A 24
4 5 C 12
4 5 C 11
5 6 D 4.5
I've tried using partition but i cant populate the result that I wanted. :crying:
Thanks In Advance.
Edited:
Updated the expected result. Forgot to put additional information. If we aggregate the records and Col3 doesnt consists of "B" value it shouldn't be aggregated also. In Essence any record that will consists of combination should be aggregated.
A and B = Aggreagate
A and A = Dont Aggregate
A and C = Dont Aggregate
C and B = Aggregate
C and C = Dont Aggregate
October 24, 2016 at 6:46 am
ramos.ferdinand (10/24/2016)
Hi Gurus,Just wanted to seek out for your help. This is a bit opposite of what aggregation should be. :hehe:
I wanted to aggregate records with different values and dont aggregate records with the same value.
Here's the code to create the test table
create table test1
(col1 int,
col2 int,
col3 varchar(2),
col4 decimal(3,1)
)
--Test Data
insert
into test1(col1,col2,col3,col4)
values (1,2,'A',5),
(1,2,'B',5),
(2,3,'B',6),
(2,3,'B',6.5),
(2,3,'B',6.3),
(3,4,'B',6),
(3,4,'B',6),
(3,4,'A',6),
(3,4,'B',6)
The result I wanted is:
Col1 Col2 Col3 Col4
1 2 A 10
2 3 B 6
2 3 B 6.5
2 3 B 6.3
3 4 A 24
I've tried using partition but i cant populate the result that I wanted. :crying:
Thanks In Advance.
Please describe how (1,2,A,10) can be derived from your source data.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 24, 2016 at 6:58 am
Quick suggestion with a CTE for grouping
😎
USE TEEST;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.test1') IS NOT NULL DROP TABLE dbo.test1;
create table dbo.test1
(col1 int,
col2 int,
col3 varchar(2),
col4 decimal(3,1)
);
--Test Data
insert
into dbo.test1(col1,col2,col3,col4)
values
(1,2,'A',5),
(1,2,'B',5),
(2,3,'B',6),
(2,3,'B',6.5),
(2,3,'B',6.3),
(3,4,'B',6),
(3,4,'B',6),
(3,4,'A',6),
(3,4,'B',6);
;WITH BASE_DATA AS
(
SELECT
T1.col1
,T1.col2
,T1.col3
,T1.col4
,DENSE_RANK() OVER
(
ORDER BY T1.col1
,T1.col2
--,T1.col3
,T1.col4
) AS GRP_RID
FROM dbo.test1 T1
)
SELECT
T1.col1
,T1.col2
,MIN(T1.col3) AS Col3
,SUM(T1.col4) AS Col4
FROM BASE_DATA T1
GROUP BY T1.col1
,T1.col2
,T1.GRP_RID;
Output
col1 col2 Col3 Col4
----- ----- ---- ------
1 2 A 10.0
2 3 B 6.0
2 3 B 6.3
2 3 B 6.5
3 4 A 24.0
October 24, 2016 at 6:59 am
Phil Parkin (10/24/2016)
ramos.ferdinand (10/24/2016)
Hi Gurus,Just wanted to seek out for your help. This is a bit opposite of what aggregation should be. :hehe:
I wanted to aggregate records with different values and dont aggregate records with the same value.
Here's the code to create the test table
create table test1
(col1 int,
col2 int,
col3 varchar(2),
col4 decimal(3,1)
)
--Test Data
insert
into test1(col1,col2,col3,col4)
values (1,2,'A',5),
(1,2,'B',5),
(2,3,'B',6),
(2,3,'B',6.5),
(2,3,'B',6.3),
(3,4,'B',6),
(3,4,'B',6),
(3,4,'A',6),
(3,4,'B',6)
The result I wanted is:
Col1 Col2 Col3 Col4
1 2 A 10
2 3 B 6
2 3 B 6.5
2 3 B 6.3
3 4 A 24
I've tried using partition but i cant populate the result that I wanted. :crying:
Thanks In Advance.
Please describe how (1,2,A,10) can be derived from your source data.
The implied rule seems to be the MIN of Col3
😎
October 24, 2016 at 8:14 am
The implied rule seems to be the MIN of Col3
Bravo. Gave me too much of a headache before my first coffee trying to work that out.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 24, 2016 at 8:38 am
A different approach, just for the sake of having options.
WITH CTE AS(
SELECT *, RANK() OVER( PARTITION BY col1, col2 ORDER BY col3) rnk
FROM test1 t1
)
SELECT col1, col2, col3, t1.col4 + ISNULL( x.sumcol4, 0) AS col4
FROM CTE t1
OUTER APPLY ( SELECT SUM( col4) sumcol4
FROM test1 i
WHERE t1.col1 = i.col1
AND t1.col2 = i.col2
AND t1.col3 <> i.col3) x
WHERE t1.rnk = 1;
October 25, 2016 at 4:58 am
thanks for the immediate response.
Updated the expected result. Forgot to put additional information. If we aggregate the records and Col3 doesn't consists of "B" value it shouldn't be aggregated also. In Essence any record that will consists of combination should be aggregated.
A and B = Aggregate
A and A = Don't Aggregate
A and C = Don't Aggregate
C and B = Aggregate
C and C = Don't Aggregate
October 25, 2016 at 5:27 am
Phil Parkin (10/24/2016)
The implied rule seems to be the MIN of Col3
Bravo. Gave me too much of a headache before my first coffee trying to work that out.
I agree, Phil. I didn't get it either from what was written.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply