Results 1 to 11 of 11
  1. #1
    Join Date
    May 2004
    Posts
    34

    Lightbulb Unanswered: Get Unique Values in a group statement

    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 Perrakis

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Manolis Perakis
    Does anyone has an idea how this can be achieved?
    Ooh ooh ooh - me - pick me!

    http://sqljunkies.com/WebLog/amachan...x?Pending=true
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    May 2004
    Posts
    34
    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

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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.
    Last edited by pootle flump; 07-23-06 at 15:27.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    May 2004
    Posts
    34
    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.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Manolis Perakis
    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?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    May 2004
    Posts
    34
    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

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't suppose there is a finite, known and ideally small number of possible "color" values?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    May 2004
    Posts
    34
    No, the "color" values is large, about 25000.

  10. #10
    Join Date
    Feb 2004
    Posts
    88
    you can get these things in 25000 different colours? Henry Ford would turn in his grave...

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Manolis Perakis
    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:
    Code:
    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.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •