# Thread: A little puzzle :P (aggregates)

1. Registered User
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. Registered User
Join Date
May 2005
Location
Posts
2,888
Try

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

3. Registered User
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. Registered User
Join Date
May 2005
Location
Posts
2,888