June 28, 2013 at 10:54 am
Hey guys,
New to the site, new to writing SQL queries and have no background in this type of thing.
But I've been googling my way to victory for the most part.
I'm struggling on probably a very simple problem.
I'd like to Sum the contents of a column, if a different value in the row is the same as in another... That was worded horribly - how about an illustration:
Input Table:
1. Unit ID Value
2. 1 20
3. 1 30
4. 1 20
5. 1 30
6. 1 10
7. 2 51
8. 3 50
9. 4 20
10. 4 30
11. 4 10
12. 4 5
13. 4 50
14. 5 5
15. 5 50
16. 6 5
17. 6 3
18. 6 50
19. 7 35
20. 7 4
So basically I'm looking for an output when I run the script that shows
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.
Expected output :
1. ID Value Indicator flag
2. 1 110(20+30+20+30+10) N
3. 2 51 Y
4. 3 50 Y
5. 4 115(20+30+10+5+50) N
6. 5 55 (50+5) N
7. 6 58 (50+8) N
And various other simplistic queries, but at this point I'm willing to admit that I haven't a clue what I'm doing.
FYI, the platform I using is IBM DB2 I-series.
Any help would be appreciated,
Thanks!
June 28, 2013 at 11:17 am
pongcs86 (6/28/2013)
Hey guys,New to the site, new to writing SQL queries and have no background in this type of thing.
But I've been googling my way to victory for the most part.
I'm struggling on probably a very simple problem.
I'd like to Sum the contents of a column, if a different value in the row is the same as in another... That was worded horribly - how about an illustration:
Input Table:
1. Unit ID Value
2. 1 20
3. 1 30
4. 1 20
5. 1 30
6. 1 10
7. 2 51
8. 3 50
9. 4 20
10. 4 30
11. 4 10
12. 4 5
13. 4 50
14. 5 5
15. 5 50
16. 6 5
17. 6 3
18. 6 50
19. 7 35
20. 7 4
So basically I'm looking for an output when I run the script that shows
aggregated of Value must >= 50 after sum up with ID.The flag indicator will tag with 'Y' if 1 unit >= 50
Expected output :
1. ID Value Indicator flag
2. 1 110(20+30+20+30+10) N
3. 2 51 Y
4. 3 50 Y
5. 4 115(20+30+10+5+50) N
6. 5 55 (50+5) N
7. 6 58 (50+8) N
And various other simplistic queries, but at this point I'm willing to admit that I haven't a clue what I'm doing.
Any help would be appreciated,
Thanks!
I think that you are attempting to sum the value by ID, and then populate the Indicator Flag based on whether any single row for the ID contains a value >= 50. If I am correct, your expected output doesn't match that. ID 4 sums to 115 and has a single row = 50 --- so that should show a flag of "Y" I think?
EDIT - or do you only expect a flag of "Y" if there is only a single row for the ID and it has a value >= 50?
June 28, 2013 at 11:17 am
Welcome to this forum,
I really hope that we can help you, but there's something that I can't understand. You say: "The flag indicator will tag with 'Y' if 1 unit >= 50"
Do you mean that only when there's a single unit? or when any unit is >= 50?
To get better help, you should give us DDL and sample data in a consumable format. Since you're new to this forum and SQL, I'll do it for you, but I recommend you to read the article linked in my signature.
CREATE TABLE #Test (
Unit_ID int,
Value int)
INSERT #Test VALUES
(1, 20),
(1, 30),
(1, 20),
(1, 30),
(1, 10),
(2, 51),
(3, 50),
(4, 20),
(4, 30),
(4, 10),
(4, 5),
(4, 50),
(5, 5),
(5, 50),
(6, 5),
(6, 3),
(6, 50),
(7, 35),
(7, 4)
SELECT *
FROM #Test
DROP TABLE #Test
June 28, 2013 at 11:29 am
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
June 28, 2013 at 11:30 am
batgirl (6/28/2013)
pongcs86 (6/28/2013)
Hey guys,New to the site, new to writing SQL queries and have no background in this type of thing.
But I've been googling my way to victory for the most part.
I'm struggling on probably a very simple problem.
I'd like to Sum the contents of a column, if a different value in the row is the same as in another... That was worded horribly - how about an illustration:
Input Table:
1. Unit ID Value
2. 1 20
3. 1 30
4. 1 20
5. 1 30
6. 1 10
7. 2 51
8. 3 50
9. 4 20
10. 4 30
11. 4 10
12. 4 5
13. 4 50
14. 5 5
15. 5 50
16. 6 5
17. 6 3
18. 6 50
19. 7 35
20. 7 4
So basically I'm looking for an output when I run the script that shows
aggregated of Value must >= 50 after sum up with ID.The flag indicator will tag with 'Y' if 1 unit >= 50
Expected output :
1. ID Value Indicator flag
2. 1 110(20+30+20+30+10) N
3. 2 51 Y
4. 3 50 Y
5. 4 115(20+30+10+5+50) N
6. 5 55 (50+5) N
7. 6 58 (50+8) N
And various other simplistic queries, but at this point I'm willing to admit that I haven't a clue what I'm doing.
Any help would be appreciated,
Thanks!
I think that you are attempting to sum the value by ID, and then populate the Indicator Flag based on whether any single row for the ID contains a value >= 50. If I am correct, your expected output doesn't match that. ID 4 sums to 115 and has a single row = 50 --- so that should show a flag of "Y" I think?
EDIT - or do you only expect a flag of "Y" if there is only a single row for the ID and it has a value >= 50?
Hi Batgirl, I excepted the flag of "Y" only single unit number value >= 50 without sum with other. Like id 2 & 3 without sum out by other value.
June 28, 2013 at 11:32 am
Then the script I posted above would work.
June 28, 2013 at 11:44 am
Luis Cazares (6/28/2013)
Welcome to this forum,I really hope that we can help you, but there's something that I can't understand. You say: "The flag indicator will tag with 'Y' if 1 unit >= 50"
Do you mean that only when there's a single unit? or when any unit is >= 50?
To get better help, you should give us DDL and sample data in a consumable format. Since you're new to this forum and SQL, I'll do it for you, but I recommend you to read the article linked in my signature.
CREATE TABLE #Test (
Unit_ID int,
Value int)
INSERT #Test VALUES
(1, 20),
(1, 30),
(1, 20),
(1, 30),
(1, 10),
(2, 51),
(3, 50),
(4, 20),
(4, 30),
(4, 10),
(4, 5),
(4, 50),
(5, 5),
(5, 50),
(6, 5),
(6, 3),
(6, 50),
(7, 35),
(7, 4)
SELECT *
FROM #Test
DROP TABLE #Test
Hi Luis , only display "Y" flag if there is a single unit_id with value >= 50 (without sum up other value)
June 29, 2013 at 9:38 am
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,
July 1, 2013 at 7:52 am
This really isn't too bad but in order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 10, 2013 at 11:53 am
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,
CASE
WHEN COUNT(Unit_ID) = 1
AND SUM(Value) >= 50
THEN max(orderID)
ELSE NULL
END AS OrderID,
CASE
WHEN COUNT(Unit_ID) = 1
AND SUM(Value) >= 50
THEN max(orderdate)
ELSE NULL
END AS OrderDate
FROM Test
GROUP BY unit_id
HAVING SUM(value) >= 50
EDIT - I assumed that the new requirement was also using "50" rather than "10"?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply