batgirl (6/28/2013)
Here is my attempt - summing the value by unit_id, displaying a "Y" flag if there is a single unit_id with value >= 50 and only displaying results if the sum of value is >= 50.
SELECT unit_id,
SUM(value) AS unit_total,
CASE
WHEN COUNT(Unit_ID) = 1
AND SUM(value) >= 50
THEN 'Y'
ELSE 'N'
END AS Flag
FROM Test
GROUP BY unit_id
HAVING SUM(value) >= 50
Thanks BatGirl for help, It worked ,really appreciated it. But my client require another requirement, wonder it can be done easier?
input table
[font="Courier New"]
1. Unit ID Value orderid orderdate
2. 1 20 12 20121121
3. 1 30 13 20121121
4. 1 20 14 20121121
5. 1 30 15 20121120
6. 1 10 16 20121121
7. 2 51 17 20121113
8. 3 50 18 20121114
9. 4 20 19 20121112
10. 4 30 20 20121112
11. 4 10 21 20121112
12. 4 5 22 20121112
13. 4 50 23 20121112
14. 5 5 25 20121110
15. 5 50 26 20121111
16. 6 5 27 20121112
17. 6 3 28 20121112
18. 6 50 29 20121114
19. 7 35 30 20121112
20. 7 4 31 20121112[/font]
Output:
aggregated of Value must >= 50 after sum up with ID.The flag indicator will tag with 'Y' if only when single unit >= 50 without sum with each other.
The single unit >=50 will shown orderID and orderdate, not single unit >=50(sum up with other value) will shown nothing.
Expected output :
[font="Courier New"]
1. ID Value Indicator flag OderID oderDate
2. 1 110(20+30+20+30+10) N
3. 2 51 Y 17 20121113
4. 3 50 Y 18 20121114
5. 4 115(20+30+10+5+50) N
6. 5 55 (50+5) N
7. 6 58 (50+8) N [/font]
Thanks,