Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2006
    Posts
    28

    Unanswered: A little puzzle :P (aggregates)

    I'll make it very short. Here's my table:

    Code:
    PRODUCT	WEIGHT	ROTTEN	CODE
    Bberry	22.8	FALSE	1A
    Bberry	15	FALSE	1B
    Bberry	10	FALSE	1C
    Peach	39.5	FALSE	2A
    Melon	120	TRUE	3A
    Banana	29.5	FALSE	4A
    Banana	18	TRUE	4B
    Rules:
    if row is not repeated (group by PRODUCT), then show row as-is
    if row is repeated:
    - sum all WEIGHTS
    - if there exists a ROTTEN = TRUE, then pick TRUE, else FALSE
    - pick the first CODE

    Results:

    Code:
    PRODUCT	WEIGHT	ROTTEN	CODE
    Bberry	47.8	FALSE	1A
    Peach	39.5	FALSE	2A
    Melon	120	TRUE	3A
    Banana	47.5	TRUE	4A
    Let's see if there's an efficient approach to this. Thanks!

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try

    SELECT Product, Sum(Weight) As TotalWeight, Min(Rotten) As IsRotten, Min(Code) As MinCode
    FROM TableName
    GROUP BY Product
    Paul

  3. #3
    Join Date
    Jan 2006
    Posts
    28
    It's funny, just when I found the solution I refreshed this page and bam got your response too!

    Thank you!

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem! Glad you sorted it out.
    Paul

Tags for this Thread

Posting Permissions

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