Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need Help on SQL query script on sum Expand / Collapse
Author
Message
Posted Friday, June 28, 2013 10:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 13, 2013 3:32 AM
Points: 4, Visits: 41
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!
Post #1468629
Posted Friday, June 28, 2013 11:17 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:40 AM
Points: 1,336, Visits: 1,505
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?
Post #1468641
Posted Friday, June 28, 2013 11:17 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 7:02 PM
Points: 3,313, Visits: 7,151
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




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1468642
Posted Friday, June 28, 2013 11:29 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:40 AM
Points: 1,336, Visits: 1,505
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

Post #1468647
Posted Friday, June 28, 2013 11:30 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 13, 2013 3:32 AM
Points: 4, Visits: 41
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.
Post #1468648
Posted Friday, June 28, 2013 11:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:40 AM
Points: 1,336, Visits: 1,505
Then the script I posted above would work.
Post #1468650
Posted Friday, June 28, 2013 11:44 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 13, 2013 3:32 AM
Points: 4, Visits: 41
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)
Post #1468653
Posted Saturday, June 29, 2013 9:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 13, 2013 3:32 AM
Points: 4, Visits: 41
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

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




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 :

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



Thanks,
Post #1468800
Posted Monday, July 1, 2013 7:52 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:50 PM
Points: 13,062, Visits: 11,891
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1469069
Posted Wednesday, July 10, 2013 11:53 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 8:40 AM
Points: 1,336, Visits: 1,505
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"?
Post #1472275
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse