Reset the total to zero when ItemCount is >= 6

  • Here's my SQL query: I want to have a sum of the item count reset to 0 when the sum >= 6.

    Please suggest what i can use to get the result.

    Select 4 ItemCount UNION ALL

    Select 3 ItemCount UNION ALL

    Select 5 ItemCount UNION ALL

    Select 2 ItemCount UNION ALL

    Select 6 ItemCount UNION ALL

    Select 3 ItemCount UNION ALL

    Select 7 ItemCount UNION ALL

    Select 1 ItemCount UNION ALL

    Select 3 ItemCount UNION ALL

    Select 3 ItemCount

     

    Expected output is:

    Select 4 ItemCount, 0 as SumTotal UNION ALL

    Select 3 ItemCount, 7 as SumTotal UNION ALL

    Select 5 ItemCount, 0 as SumTotal UNION ALL

    Select 2 ItemCount, 7 as SumTotal UNION ALL

    Select 6 ItemCount, 6 as SumTotal UNION ALL

    Select 3 ItemCount, 0 as SumTotal UNION ALL

    Select 7 ItemCount, 10 as SumTotal UNION ALL

    Select 1 ItemCount, 0 as SumTotal UNION ALL

    Select 3 ItemCount, 0 as SumTotal UNION ALL

    Select 3 ItemCount, 7 as SumTotal

    • This topic was modified 1 year, 9 months ago by  ziangij.
    • This topic was modified 1 year, 9 months ago by  ziangij. Reason: corrected query
  • Your first 'query' is actually 10 separate queries producing 10 discrete result sets.

    Assuming you're just going to UNION ALL the first query, this is still impossible to solve without adding in a column which allows ordering of the source data.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • The original table has identity column. Does this help?

    Select 1 as ID,4 ItemCount UNION ALL

    Select 2 as ID,3 ItemCount UNION ALL

    Select 3 as ID,5 ItemCount UNION ALL

    Select 4 as ID,2 ItemCount UNION ALL

    Select 5 as ID,6 ItemCount UNION ALL

    Select 6 as ID,3 ItemCount UNION ALL

    Select 7 as ID,7 ItemCount UNION ALL

    Select 8 as ID,1 ItemCount UNION ALL

    Select 9 as ID,3 ItemCount UNION ALL

    Select 10 as ID,3 ItemCount

  • >> I want to have a sum of the item count reset to 0 when the sum >= 6. <<

    Did you know that a table must have a key by definition? It is not an option! Why are you UNION ALL in 10 separate highly proprietary queries to build a table from scratch?

    CREATE TABLE Foobar

    (foo_id CHAR(3) NOT NULL PRIMARY KEY,

    item_cnt INTEGER NOT NULL);

    Here is how we write an insert statement in SQL today.

    INSERT INTO Foobar

    VALUES

    ('001', 4),

    ('002', 3),

    ('003', 5),

    ('004', 2),

    ('005', 6),

    ('006', 3),

    ('007', 7),

    ('008', 1),

    ('009', 3),

    ('010', 3);

    Do you understand that SQL is a set oriented language? Things are not inserted sequentially into a table the table has no ordering and when you use one statement, all of the data comes in as a single unit of work all at once. You seem to think this is a deck of punch cards whose physical ordering would give you some kind of sequence to make your request understandable. But Dr. Cobb's information principle says that all such orderings and other relationships must be expressed as scalar values in the columns of rows in the table. This fundamental principle should have been covered in the first week or two of your SQL class.

    SELECT foo_id, item_cnt

    SUM(item_cnt) OVER (ORDER BY foo_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS running_item_tot

    FROM Foobar;

    This gives a running total, ordered by the foo_id. You can now take this running total and put it in a view that shows (running_item_tot % 6). I'll let you do the math.

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • jcelko212 32090 wrote:

    This gives a running total, ordered by the foo_id. You can now take this running total and put it in a view that shows (running_item_tot % 6). I'll let you do the math.

    Given that the "expected output" includes two "7" values and a "10" value, I think "Mod 6" is not what is needed.  Line 2 of the expected output sums "3" (quotes used to quote the value, not determine datatype...) from line 1 and "4" from line 2, showing "7" as the result.  "Mod 6" would never show a value of "7", so the "Mod 6" can't be the solution.

    Given the expected output, the total column should show a non-zero value only when the total goes to 6 or above.  That explains the "6" on line 5 (that one line reached 6 by itself) and the "0" on line 9 (the sum of the prior line and current line has not yet reached 6).

  • jcelko212 32090 wrote:

    >> I want to have a sum of the item count reset to 0 when the sum >= 6. <<

    Did you know that a table must have a key by definition? It is not an option! Why are you UNION ALL in 10 separate highly proprietary queries to build a table from scratch?

    CREATE TABLE Foobar

    (foo_id CHAR(3) NOT NULL PRIMARY KEY,

    item_cnt INTEGER NOT NULL);

    Here is how we write an insert statement in SQL today.

    INSERT INTO Foobar

    VALUES

    ('001', 4),

    ('002', 3),

    ('003', 5),

    ('004', 2),

    ('005', 6),

    ('006', 3),

    ('007', 7),

    ('008', 1),

    ('009', 3),

    ('010', 3);

    Do you understand that SQL is a set oriented language? Things are not inserted sequentially into a table the table has no ordering and when you use one statement, all of the data comes in as a single unit of work all at once. You seem to think this is a deck of punch cards whose physical ordering would give you some kind of sequence to make your request understandable. But Dr. Cobb's information principle says that all such orderings and other relationships must be expressed as scalar values in the columns of rows in the table. This fundamental principle should have been covered in the first week or two of your SQL class.

    SELECT foo_id, item_cnt

    SUM(item_cnt) OVER (ORDER BY foo_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)) AS running_item_tot

    FROM Foobar;

    This gives a running total, ordered by the foo_id. You can now take this running total and put it in a view that shows (running_item_tot % 6). I'll let you do the math.

    BWAAAA-HAAAA-HAAAA-HAAAA!!!!! Lookie there!!! Character based or not, you have just condoned the use of a numeric-based "ID" column simply by, first, not condemning it and, second, by using it to control the order of rows in the code your wrote!  You can just bet that I've book marked this one. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • jcelko212 32090 wrote:

    Did you know that a table must have a key by definition? It is not an option!

    Actually, it IS an option.  HEAPs work just fine for things like Audit tables. 😉

    jcelko212 32090 wrote:

    Why are you UNION ALL in 10 separate highly proprietary queries to build a table from scratch?

    First of all, SELECT/UNION all is anything but "highly proprietary".  It's a part of the ANSI standard, is it not?  At the very least, there are a great number of RDBMSs that support it.

    Second, it should be obvious that the OP is only providing test data and simply didn't put it into a table.  Lighten up!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • jcelko212 32090 wrote:

    This gives a running total, ordered by the foo_id. You can now take this running total and put it in a view that shows (running_item_tot % 6). I'll let you do the math.

    He he, how do you handle a single entry that fills or over fills the bucket, mod will not detect that 😉

    😎

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply