Hi,
Suppose a table [Inventory]:
Item Color Quantity
------- ------- --------
Table Blue 10
Table Red 20
Table Yellow 30
Chair Blue 40
Chair Red 50
I'm wondering if there is a group state like this:
Select Item, ?Function(Color), Sum(Quantity) From Inventory Group by Item
which returns this:
Table Blue,Red,Yellow 60
Chair Blue,Red 90
Does anyone has an idea how this can be achieved?
Regards,
Manolis PerrakisDoes anyone has an idea how this can be achieved?Ooh ooh ooh - me - pick me!
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true
:)|||Hi,
thanks for the answer.
I don't think is a good idea to run a query for each record. This will consume a lot of resources, and in my case when having to do with large tables and a lot of resulted records this is not an option.
I was hoping there was an internal function, of if it can be defined such a funtion. For example if AVG is used the MSSQL access all the records keep their values and at the end calculates the result. Instead of adding these values I was hoping to create a string which can be compared internally without having to execute another query.
Reagrds,
Manolis Perrakis|||Search the text for "Yep, me too... until I tuned it and then it did it all in 5 seconds flat on a million rows for 50,000 CustID's. " and see if the suggested optimisations are appropriate for you.
Ultimately you are taking relational data and trying to put it into a context that violates first normal form so it is not surprising that SQL does not provide a built in function to do this.
The other alternative is that you can write (if you are using SQL Server 2005) your own CLR aggregate functions. I don't really know anything about these (including how they perform) but you could try researching to see if it is applicable to your needs.|||Yes you are right. There is the article:
http://msdn2.microsoft.com/en-us/library/ms131056.aspx
that does exactly this.
However I use MSSQL2000.|||However I use MSSQL2000.I suspect you are probably stuffed then.
I like the article I linked to as it demonstrates, and compares, two methods of skinning this particular cat. In particular it focuses on performance and, as I pointed out, later on one contributer offers a few refinements that get a decent performance for a medium sized table (1 million rows).
I doubt you will find any alternative technique that will substantially outperform the solution in the article but maybe one of the posters here will surprise me :)
BTW - what sort of performance did you get?|||Hi,
I want to use this operation in some aggregate complex queries that are already slow. Apart from this in order to get the correct data when calculating these value I must run these queries with other criteria also, such as date range which must be used in order to get the correct records. Therefore it's quite complicated. However I will try it the next days.
Regards,
Manolis|||I don't suppose there is a finite, known and ideally small number of possible "color" values?|||No, the "color" values is large, about 25000.|||you can get these things in 25000 different colours? Henry Ford would turn in his grave...|||I don't think is a good idea to run a query for each record. Just thought - I don't know how SQL Server optimises the query for this but presumably does as you suggest - runs the function for each record and then groups on the results. So you could improve things (I imagine) with something like:
SELECT Item, dbo.MyConcatFunction(Item) AS CSV_Colors, TotalQuantity
FROM (
SELECT Item, SUM(Quantity) AS TotalQuantity
FROM [Inventory]
GROUP BY Item
) AS Distinct_Items
Call the function once per item rather than once per row.
