SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need Help on SQL query script on sum


Need Help on SQL query script on sum

Author
Message
pongcs86
pongcs86
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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!
batgirl
batgirl
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1705 Visits: 1820
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?
Luis Cazares
Luis Cazares
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16351 Visits: 19076
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.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
batgirl
batgirl
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1705 Visits: 1820
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


pongcs86
pongcs86
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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.
batgirl
batgirl
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1705 Visits: 1820
Then the script I posted above would work.
pongcs86
pongcs86
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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)
pongcs86
pongcs86
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
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,
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25924 Visits: 17519
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.

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)
batgirl
batgirl
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1705 Visits: 1820

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"?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search