May 10, 2015 at 3:50 am
Hi,
I have this data as below. I need to find out the combination from the data and take a count of them
CREATE TABLE A
( nRef INT,
nOrd INT,
Token INT,
nML INT,
nNode INT,
sSymbol VARCHAR(50),
nMessageCode INT
)
INSERT INTO A
( nReferenceNumber,nOrderNumber,nTokenNumber,nML,nNode,sSymbol,nMessageCode )
VALUES
(1, 101, 1001,0,2,'SILVER',13073),
(1, 101, 1001,0,2,'SILVER',13075),
(1, 102, 1003,0,2,'CASTORSEED',13073),
(1, 102, 1003,0,2,'CASTORSEED',13075),
(2, 103, 1001,1,3,'SILVER',13073),
(2, 104, 1004,1,3,'BAJRA', 13073),
(2, 105, 1005,1,3,'CASTORSEED',13073),
(2, 103, 1001,1,3,'SILVER',13075),
(2, 104, 1004,1,3,'BAJRA', 13075),
(2, 105, 1005,1,3,'CASTORSEED',13075),
(2, 103, 1001,1,3,'SILVER',13333),
(2, 104, 1004,1,3,'BAJRA', 13333),
(2, 105, 1005,1,3,'CASTORSEED',13333),
(3, 107, 1003,0,2,'CASTORSEED',13073),
(3, 107, 1003,0,2,'CASTORSEED',13333),
(3, 106, 1001,0,2,'SILVER',13073),
(3, 106, 1001,0,2,'SILVER',13333)
(4, 108, 1004,1,3,'BAJRA', 13073),
(4, 109, 1008,1,3,'BAJRA', 13073),
(4, 110, 1009,1,3,'BAJRA', 13073),
(4, 108, 1004,1,3,'BAJRA', 13075),
(4, 109, 1008,1,3,'BAJRA', 13075),
(4, 110, 1009,1,3,'BAJRA', 13075),
(5, 111, 1003,0,2,'CASTORSEED',13073),
(5, 112, 1011,0,2,'STEELFLAT',13073),
(5, 111, 1003,0,2,'CASTORSEED',13333),
(5, 112, 1011,0,2,'STEELFLAT',13333),
(5, 111, 1003,0,2,'CASTORSEED',13075),
(5, 112, 1011,0,2,'STEELFLAT',13075),
if you can see, the rows with column nRefNo 1 and 3 are same i.e. with same combination of Symbol viz. Silver and Castorseed.
Hence the desired output will be
Symbol Count
Castorseed-Silver 8
How to get this combination together and then take count of them. Please note i will be dealing with more than 5 million rows.
Thanks
Saumik Vora
May 10, 2015 at 4:35 am
Quick solution which should be enough to get you passed this hurdle
😎
USE tempdb;
GO
SET NOCOUNT ON;
IF OBJECT_ID(N'dbo.TBL_A') IS NOT NULL DROP TABLE dbo.TBL_A;
CREATE TABLE dbo.TBL_A
(
nRef INT NOT NULL
,nOrd INT NOT NULL
,Token INT NOT NULL
,nML INT NOT NULL
,nNode INT NOT NULL
,sSymbol VARCHAR(50) NOT NULL
,nMessageCode INT NOT NULL
)
INSERT INTO dbo.TBL_A ( nRef,nOrd,Token,nML,nNode,sSymbol,nMessageCode )
VALUES
(1, 101, 1001,0,2,'SILVER' ,13073)
,(1, 101, 1001,0,2,'SILVER' ,13075)
,(1, 102, 1003,0,2,'CASTORSEED' ,13073)
,(1, 102, 1003,0,2,'CASTORSEED' ,13075)
,(2, 103, 1001,1,3,'SILVER' ,13073)
,(2, 104, 1004,1,3,'BAJRA' ,13073)
,(2, 105, 1005,1,3,'CASTORSEED' ,13073)
,(2, 103, 1001,1,3,'SILVER' ,13075)
,(2, 104, 1004,1,3,'BAJRA' ,13075)
,(2, 105, 1005,1,3,'CASTORSEED' ,13075)
,(2, 103, 1001,1,3,'SILVER' ,13333)
,(2, 104, 1004,1,3,'BAJRA' ,13333)
,(2, 105, 1005,1,3,'CASTORSEED' ,13333)
,(3, 107, 1003,0,2,'CASTORSEED' ,13073)
,(3, 107, 1003,0,2,'CASTORSEED' ,13333)
,(3, 106, 1001,0,2,'SILVER' ,13073)
,(3, 106, 1001,0,2,'SILVER' ,13333)
,(4, 108, 1004,1,3,'BAJRA' ,13073)
,(4, 109, 1008,1,3,'BAJRA' ,13073)
,(4, 110, 1009,1,3,'BAJRA' ,13073)
,(4, 108, 1004,1,3,'BAJRA' ,13075)
,(4, 109, 1008,1,3,'BAJRA' ,13075)
,(4, 110, 1009,1,3,'BAJRA' ,13075)
,(5, 111, 1003,0,2,'CASTORSEED' ,13073)
,(5, 112, 1011,0,2,'STEELFLAT' ,13073)
,(5, 111, 1003,0,2,'CASTORSEED' ,13333)
,(5, 112, 1011,0,2,'STEELFLAT' ,13333)
,(5, 111, 1003,0,2,'CASTORSEED' ,13075)
,(5, 112, 1011,0,2,'STEELFLAT' ,13075)
;
;WITH BASE_DATA AS
(
SELECT
TA.nRef
,COUNT(TA.sSymbol) AS SYMBOL_COUNT
,STUFF((SELECT DISTINCT
CHAR(45) + T.sSymbol
FROM dbo.TBL_A T
WHERE TA.nRef = T.nRef
FOR XML PATH(''),TYPE).value('.[1]','VARCHAR(8000)'),1,1,'') AS SYMBOL_STR
FROM dbo.TBL_A TA
GROUP BY TA.nRef
)
SELECT
BD.SYMBOL_STR
,BD.SYMBOL_COUNT
,COUNT(BD.nRef) AS INSTANCE_COUNT
,BD.SYMBOL_COUNT * COUNT(BD.nRef) AS TOTAL_COUNT
FROM BASE_DATA BD
GROUP BY BD.SYMBOL_STR
,BD.SYMBOL_COUNT;
Results
SYMBOL_STR SYMBOL_COUNT INSTANCE_COUNT TOTAL_COUNT
------------------------- ------------ -------------- -----------
CASTORSEED-SILVER 4 2 8
BAJRA 6 1 6
CASTORSEED-STEELFLAT 6 1 6
BAJRA-CASTORSEED-SILVER 9 1 9
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply