Need Help on SQL query script on sum

  • 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!

  • 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?

  • 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
  • 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

  • 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.

  • Then the script I posted above would work.

  • 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)

  • 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,

  • 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/

  • 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