Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Group by - Retaining all serial numbers Expand / Collapse
Author
Message
Posted Saturday, June 29, 2013 6:12 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, September 7, 2013 5:18 AM
Points: 25, Visits: 61
Hi,

I am trying to group a dataset by the variable Sale_Type and retain the serial numbers along:

Here is the data:

Table1:
Sno Sale_Type Amount
1 Drug 10
2 Fruit 20
3 Groceries 30
4 Drug 10
5 Fruit 20
6 Groceries 30
7 Drug 10
8 Fruit 20
9 Groceries 30
10 Drug 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.
Post #1468792
Posted Saturday, June 29, 2013 7:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 2,386, Visits: 7,611
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).



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1468795
Posted Saturday, June 29, 2013 10:06 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, September 7, 2013 5:18 AM
Points: 25, Visits: 61
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.
Post #1468806
Posted Sunday, June 30, 2013 3:24 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:28 PM
Points: 2,386, Visits: 7,611
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.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1468893
Posted Sunday, June 30, 2013 10:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, September 7, 2013 5:18 AM
Points: 25, Visits: 61
Thanks again for your time, I truly appreciate it.
Post #1468923
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse