August 27, 2019 at 12:39 pm
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
August 27, 2019 at 12:48 pm
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
August 27, 2019 at 1:17 pm
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
August 29, 2019 at 9:51 pm
>> 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.
August 30, 2019 at 8:49 pm
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).
August 31, 2019 at 2:32 am
>> 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
Change is inevitable... Change for the better is not.
August 31, 2019 at 2:51 am
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. 😉
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
Change is inevitable... Change for the better is not.
August 31, 2019 at 6:22 pm
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 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply