June 29, 2013 at 6:12 am
Hi,
I am trying to group a dataset by the variable Sale_Type and retain the serial numbers along:
Here is the data:
Table1:
SnoSale_TypeAmount
1Drug 10
2Fruit 20
3Groceries30
4Drug 10
5Fruit 20
6Groceries30
7Drug 10
8Fruit 20
9Groceries30
10Drug 10
Here is the result, I am trying to achieve:
Sale_Type Amount Sno_Retained
Drug 40 1, 4, 7, 10
Fruit 60 2, 5, 8
Groceries 90 3, 6, 9
Here is my code so far:
select Saletype, sum(Amount) from table1 group by sale_type
Any help would be truly appreciable.
Thanks & Regards,
Akber Khan.
June 29, 2013 at 7:32 am
First things first, when you visit a technical forum it is always a good idea to provide as much information as possible and to make it as easy as possible for people to help you. In a SQL forum, that means providing DDL and readily consumable sample data so that anyone wanting to help you can quickly knock up a nice quick copy of your data. In your case, this is enough: -
IF object_id('tempdb..#Table1') IS NOT NULL
BEGIN;
DROP TABLE #Table1;
END;
SELECT IDENTITY(INT,1,1) AS Sno, Sale_Type, Amount
INTO #Table1
FROM (VALUES('Drug',10),('Fruit',20),('Groceries',30),('Drug',10),
('Fruit',20),('Groceries',30),('Drug',10),('Fruit',20),
('Groceries',30),('Drug',10))a(Sale_Type, Amount);
What you're asking isn't an overly simple thing. With that in mind, this is the answer: -
SELECT Sale_Type, SUM(Amount) AS Amount, MAX(Sno_Retained) AS Sno_Retained
FROM #Table1 a
CROSS APPLY (SELECT STUFF((SELECT ', '+CAST(Sno AS VARCHAR(5))
FROM #Table1 b
WHERE a.Sale_Type = b.Sale_Type
ORDER BY Sno
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,2,''
)
) c(Sno_Retained)
GROUP BY Sale_Type;
That produces: -
Sale_Type Amount Sno_Retained
--------- ----------- --------------
Drug 40 1, 4, 7, 10
Fruit 60 2, 5, 8
Groceries 90 3, 6, 9
If all you were interested in is an answer, you can leave now 😛
Otherwise, let's take a look at what we're doing here.
First, set your results to text instead of the default grid. It'll make it easier for me to explain.
Execute this: -
IF object_id('tempdb..#Table1') IS NOT NULL
BEGIN;
DROP TABLE #Table1;
END;
SELECT IDENTITY(INT,1,1) AS Sno, Sale_Type, Amount
INTO #Table1
FROM (VALUES('Drug',10),('Fruit',20),('Groceries',30),('Drug',10),
('Fruit',20),('Groceries',30),('Drug',10),('Fruit',20),
('Groceries',30),('Drug',10))a(Sale_Type, Amount);
SELECT Sno
FROM #Table1
ORDER BY Sno
FOR XML PATH(''), TYPE;
You'll get back something like this: -
--------------------------------------------------------------------------------------------------------------------------
<Sno>1</Sno><Sno>2</Sno><Sno>3</Sno><Sno>4</Sno><Sno>5</Sno><Sno>6</Sno><Sno>7</Sno><Sno>8</Sno><Sno>9</Sno><Sno>10</Sno>
So that has concatenated all of your "Sno" into one long XML string.
Next, we'll add in the comma separators and remove the tags. We do this like this: -
IF object_id('tempdb..#Table1') IS NOT NULL
BEGIN;
DROP TABLE #Table1;
END;
SELECT IDENTITY(INT,1,1) AS Sno, Sale_Type, Amount
INTO #Table1
FROM (VALUES('Drug',10),('Fruit',20),('Groceries',30),('Drug',10),
('Fruit',20),('Groceries',30),('Drug',10),('Fruit',20),
('Groceries',30),('Drug',10))a(Sale_Type, Amount);
SELECT ', '+CAST(Sno AS VARCHAR(5))
FROM #Table1
ORDER BY Sno
FOR XML PATH(''), TYPE;
You'll get back something like this: -
---------------------------------
, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10
Now, we want to remove the comma that appears at the start of the string.
IF object_id('tempdb..#Table1') IS NOT NULL
BEGIN;
DROP TABLE #Table1;
END;
SELECT IDENTITY(INT,1,1) AS Sno, Sale_Type, Amount
INTO #Table1
FROM (VALUES('Drug',10),('Fruit',20),('Groceries',30),('Drug',10),
('Fruit',20),('Groceries',30),('Drug',10),('Fruit',20),
('Groceries',30),('Drug',10))a(Sale_Type, Amount);
SELECT STUFF((SELECT ', '+CAST(Sno AS VARCHAR(5))
FROM #Table1
ORDER BY Sno
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,2,''
);
You'll get something like this: -
-------------------------------
1, 2, 3, 4, 5, 6, 7, 8, 9, 10
Obviously, this is everything rather than added to the specific Sale_Type. Well, we do that with the CROSS APPLY.
We bring in your original query: -
IF object_id('tempdb..#Table1') IS NOT NULL
BEGIN;
DROP TABLE #Table1;
END;
SELECT IDENTITY(INT,1,1) AS Sno, Sale_Type, Amount
INTO #Table1
FROM (VALUES('Drug',10),('Fruit',20),('Groceries',30),('Drug',10),
('Fruit',20),('Groceries',30),('Drug',10),('Fruit',20),
('Groceries',30),('Drug',10))a(Sale_Type, Amount);
--SELECT STUFF((SELECT ', '+CAST(Sno AS VARCHAR(5))
-- FROM #Table1
-- ORDER BY Sno
-- FOR XML PATH(''), TYPE
-- ).value('.','NVARCHAR(MAX)'),1,2,''
-- );
SELECT Sale_Type, SUM(Amount) AS Amount
FROM #Table1 a
GROUP BY Sale_Type;
Which returns this: -
Sale_Type Amount
--------- -----------
Drug 40
Fruit 60
Groceries 90
We can then apply our XML query to your query, but we need to add a WHERE condition to the XML query to match the Sale_Type of each. This ensures that we only apply the XML query to gather the Sno for the individual Sale_Type.
IF object_id('tempdb..#Table1') IS NOT NULL
BEGIN;
DROP TABLE #Table1;
END;
SELECT IDENTITY(INT,1,1) AS Sno, Sale_Type, Amount
INTO #Table1
FROM (VALUES('Drug',10),('Fruit',20),('Groceries',30),('Drug',10),
('Fruit',20),('Groceries',30),('Drug',10),('Fruit',20),
('Groceries',30),('Drug',10))a(Sale_Type, Amount);
SELECT Sale_Type, SUM(Amount) AS Amount
FROM #Table1 a
CROSS APPLY (SELECT STUFF((SELECT ', '+CAST(Sno AS VARCHAR(5))
FROM #Table1
ORDER BY Sno
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,2,''
)
)c(Sno_Retained)
GROUP BY Sale_Type;
This still returns: -
Sale_Type Amount
--------- -----------
Drug 40
Fruit 60
Groceries 90
If we reference the c.Sno_Retained straight into the query, we'd get: -
Msg 8120, Level 16, State 1, Line 12
Column 'c.Sno_Retained' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
This is because we're aggregating the query. We could solve this in two ways, we either aggregate the c.Sno_Retained or we move the first aggregation into a subquery that we reference. So, here are our two options: -
IF object_id('tempdb..#Table1') IS NOT NULL
BEGIN;
DROP TABLE #Table1;
END;
SELECT IDENTITY(INT,1,1) AS Sno, Sale_Type, Amount
INTO #Table1
FROM (VALUES('Drug',10),('Fruit',20),('Groceries',30),('Drug',10),
('Fruit',20),('Groceries',30),('Drug',10),('Fruit',20),
('Groceries',30),('Drug',10))a(Sale_Type, Amount);
--Option 1
SELECT Sale_Type, SUM(Amount) AS Amount, MAX(c.Sno_Retained) AS Sno_Retained
FROM #Table1 a
CROSS APPLY (SELECT STUFF((SELECT ', '+CAST(Sno AS VARCHAR(5))
FROM #Table1
ORDER BY Sno
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,2,''
)
)c(Sno_Retained)
GROUP BY Sale_Type;
--Option 2
SELECT Sale_Type, Amount, Sno_Retained
FROM (SELECT Sale_Type, SUM(Amount) AS Amount
FROM #Table1
GROUP BY Sale_Type
)a
CROSS APPLY (SELECT STUFF((SELECT ', '+CAST(Sno AS VARCHAR(5))
FROM #Table1
ORDER BY Sno
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,2,''
)
)c(Sno_Retained);
Both will return the same values and almost identical execution plans (that's a guess, I haven't checked but I imagine that we'd just move the stream aggregate).
June 29, 2013 at 10:06 am
Hi,
Firstly, I Apologize for presenting the data the way I did.
I had a very complex situation for doing this and I would truly like to thank you for your help. Honestly, I would have never been able to achieve this otherwise. I knew before hand that this question would need expert review and I cant thank you enough for taking the time.
Just one final question is whether the numbers would be ordered or rather can they be ordered in the 'Sno_Retained' field.
Thanks again,
Akber.
June 30, 2013 at 3:24 pm
akberali67 (6/29/2013)
Hi,Firstly, I Apologize for presenting the data the way I did.
No problem, just remember for next time 😀
akberali67 (6/29/2013)
Just one final question is whether the numbers would be ordered or rather can they be ordered in the 'Sno_Retained' field.Thanks again,
Akber.
If you look at the code in the FOR XML bit, there is an "ORDER BY Sno". So the Sno_Retained will currently be ordered by the Sno. . . e.g, 1,2,3 rather than 3,1,2.
June 30, 2013 at 10:43 pm
Thanks again for your time, I truly appreciate it.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply