How do I calculate the sum of column when there is a condition in SQLite Swift?
I want to use SQLite3 Swift to do a calculation that accumulates values when the flag is 0 and subtracts them from the accumulated value when it is 1.
I also want to get the average of the cumulative values only if Flag is 0, and I want to get the previous average value if it is 1.
I attached the picture.
swift sqlite3
asked Nov 23 '18 at 9:21
dinggudinggu
526
add a comment |
I want to use SQLite3 Swift to do a calculation that accumulates values when the flag is 0 and subtracts them from the accumulated value when it is 1.
I also want to get the average of the cumulative values only if Flag is 0, and I want to get the previous average value if it is 1.
I attached the picture.
swift sqlite3
asked Nov 23 '18 at 9:21
dinggudinggu
526
I don't understand your logic. Can you explain it better?
– Tim Biegeleisen
Nov 23 '18 at 9:24
add a comment |
I want to use SQLite3 Swift to do a calculation that accumulates values when the flag is 0 and subtracts them from the accumulated value when it is 1.
I also want to get the average of the cumulative values only if Flag is 0, and I want to get the previous average value if it is 1.
I attached the picture.
swift sqlite3
asked Nov 23 '18 at 9:21
dinggudinggu
526
I want to use SQLite3 Swift to do a calculation that accumulates values when the flag is 0 and subtracts them from the accumulated value when it is 1.
I also want to get the average of the cumulative values only if Flag is 0, and I want to get the previous average value if it is 1.
I attached the picture.
swift sqlite3
swift sqlite3
asked Nov 23 '18 at 9:21
dinggudinggu
526
asked Nov 23 '18 at 9:21
dinggudinggu
526
asked Nov 23 '18 at 9:21
dinggudinggu
526
asked Nov 23 '18 at 9:21
dinggudinggu
526
asked Nov 23 '18 at 9:21
dinggudinggu
526
526
I don't understand your logic. Can you explain it better?
– Tim Biegeleisen
Nov 23 '18 at 9:24
add a comment |
I don't understand your logic. Can you explain it better?
– Tim Biegeleisen
Nov 23 '18 at 9:24
I don't understand your logic. Can you explain it better?
– Tim Biegeleisen
Nov 23 '18 at 9:24
I don't understand your logic. Can you explain it better?
– Tim Biegeleisen
Nov 23 '18 at 9:24
add a comment |
2 Answers
2
active
oldest
votes
If you're using Sqlite 3.25 or better, window functions make getting the RsSum results easy. It took me a while to figure out how the RsAvg numbers are calculated, but I was able to get those values too:
CREATE TABLE input(no INTEGER PRIMARY KEY, flag INTEGER, amt NUMERIC);
INSERT INTO input(no, flag, amt)
VALUES (1, 0, 10), (2, 0, 20), (3, 0, 30), (4, 1, 10), (5, 0, 10), (6, 0, 20)
, (7, 1, 30);
WITH
sumcte AS (
SELECT no, flag
, sum(CASE WHEN flag = 0 THEN amt ELSE -amt END) OVER (ORDER BY no) AS RsSum
FROM input)
, avgcte AS (
SELECT no, flag, RsSum
, cast(RsSum AS REAL) / count(no) FILTER (WHERE flag = 0)
OVER (ORDER BY no) As RsAvg
FROM sumcte)
SELECT RsSum
, (CASE WHEN flag = 0 THEN RsAvg
ELSE lag(RsAvg, 1) OVER (ORDER BY no) END) As RsAvg
FROM avgcte ORDER BY no;
produces
RsSum RsAvg
---------- ----------
10 10.0
30 15.0
60 20.0
50 20.0
60 15.0
80 16.0
50 16.0
(Interpreting RsAvg as that row's RsSum divided by the number of rows seen so far where flag is 0 is the only way I could make the numbers work.)
This will break when two rows in a row have a flag of 1, though. I'll update again if I figure a workaround for that case.
answered Nov 23 '18 at 9:49
ShawnShawn
4,0971613
Your answer was a big help. thank you.
– dinggu
Nov 23 '18 at 15:18
add a comment |
You can filter the entities based on the condition (flag == 0) using some NSPredicate kind of function.
Then you can add the values of the sequence (sum1) using the reduce(_:_:) function.
Now that you have the sum1, you can similarly filter the (flag == 1) entities.
Use reduce function again on the sequence to get their sum2. Subtract sum2 from sum.
answered Nov 23 '18 at 9:32
DamonDamon
520318
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53443766%2fhow-do-i-calculate-the-sum-of-column-when-there-is-a-condition-in-sqlite-swift%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
If you're using Sqlite 3.25 or better, window functions make getting the RsSum results easy. It took me a while to figure out how the RsAvg numbers are calculated, but I was able to get those values too:
CREATE TABLE input(no INTEGER PRIMARY KEY, flag INTEGER, amt NUMERIC);
INSERT INTO input(no, flag, amt)
VALUES (1, 0, 10), (2, 0, 20), (3, 0, 30), (4, 1, 10), (5, 0, 10), (6, 0, 20)
, (7, 1, 30);
WITH
sumcte AS (
SELECT no, flag
, sum(CASE WHEN flag = 0 THEN amt ELSE -amt END) OVER (ORDER BY no) AS RsSum
FROM input)
, avgcte AS (
SELECT no, flag, RsSum
, cast(RsSum AS REAL) / count(no) FILTER (WHERE flag = 0)
OVER (ORDER BY no) As RsAvg
FROM sumcte)
SELECT RsSum
, (CASE WHEN flag = 0 THEN RsAvg
ELSE lag(RsAvg, 1) OVER (ORDER BY no) END) As RsAvg
FROM avgcte ORDER BY no;
produces
RsSum RsAvg
---------- ----------
10 10.0
30 15.0
60 20.0
50 20.0
60 15.0
80 16.0
50 16.0
(Interpreting RsAvg as that row's RsSum divided by the number of rows seen so far where flag is 0 is the only way I could make the numbers work.)
This will break when two rows in a row have a flag of 1, though. I'll update again if I figure a workaround for that case.
answered Nov 23 '18 at 9:49
ShawnShawn
4,0971613
Your answer was a big help. thank you.
– dinggu
Nov 23 '18 at 15:18
add a comment |
If you're using Sqlite 3.25 or better, window functions make getting the RsSum results easy. It took me a while to figure out how the RsAvg numbers are calculated, but I was able to get those values too:
CREATE TABLE input(no INTEGER PRIMARY KEY, flag INTEGER, amt NUMERIC);
INSERT INTO input(no, flag, amt)
VALUES (1, 0, 10), (2, 0, 20), (3, 0, 30), (4, 1, 10), (5, 0, 10), (6, 0, 20)
, (7, 1, 30);
WITH
sumcte AS (
SELECT no, flag
, sum(CASE WHEN flag = 0 THEN amt ELSE -amt END) OVER (ORDER BY no) AS RsSum
FROM input)
, avgcte AS (
SELECT no, flag, RsSum
, cast(RsSum AS REAL) / count(no) FILTER (WHERE flag = 0)
OVER (ORDER BY no) As RsAvg
FROM sumcte)
SELECT RsSum
, (CASE WHEN flag = 0 THEN RsAvg
ELSE lag(RsAvg, 1) OVER (ORDER BY no) END) As RsAvg
FROM avgcte ORDER BY no;
produces
RsSum RsAvg
---------- ----------
10 10.0
30 15.0
60 20.0
50 20.0
60 15.0
80 16.0
50 16.0
(Interpreting RsAvg as that row's RsSum divided by the number of rows seen so far where flag is 0 is the only way I could make the numbers work.)
This will break when two rows in a row have a flag of 1, though. I'll update again if I figure a workaround for that case.
answered Nov 23 '18 at 9:49
ShawnShawn
4,0971613
Your answer was a big help. thank you.
– dinggu
Nov 23 '18 at 15:18
add a comment |
If you're using Sqlite 3.25 or better, window functions make getting the RsSum results easy. It took me a while to figure out how the RsAvg numbers are calculated, but I was able to get those values too:
CREATE TABLE input(no INTEGER PRIMARY KEY, flag INTEGER, amt NUMERIC);
INSERT INTO input(no, flag, amt)
VALUES (1, 0, 10), (2, 0, 20), (3, 0, 30), (4, 1, 10), (5, 0, 10), (6, 0, 20)
, (7, 1, 30);
WITH
sumcte AS (
SELECT no, flag
, sum(CASE WHEN flag = 0 THEN amt ELSE -amt END) OVER (ORDER BY no) AS RsSum
FROM input)
, avgcte AS (
SELECT no, flag, RsSum
, cast(RsSum AS REAL) / count(no) FILTER (WHERE flag = 0)
OVER (ORDER BY no) As RsAvg
FROM sumcte)
SELECT RsSum
, (CASE WHEN flag = 0 THEN RsAvg
ELSE lag(RsAvg, 1) OVER (ORDER BY no) END) As RsAvg
FROM avgcte ORDER BY no;
produces
RsSum RsAvg
---------- ----------
10 10.0
30 15.0
60 20.0
50 20.0
60 15.0
80 16.0
50 16.0
(Interpreting RsAvg as that row's RsSum divided by the number of rows seen so far where flag is 0 is the only way I could make the numbers work.)
This will break when two rows in a row have a flag of 1, though. I'll update again if I figure a workaround for that case.
answered Nov 23 '18 at 9:49
ShawnShawn
4,0971613
If you're using Sqlite 3.25 or better, window functions make getting the RsSum results easy. It took me a while to figure out how the RsAvg numbers are calculated, but I was able to get those values too:
CREATE TABLE input(no INTEGER PRIMARY KEY, flag INTEGER, amt NUMERIC);
INSERT INTO input(no, flag, amt)
VALUES (1, 0, 10), (2, 0, 20), (3, 0, 30), (4, 1, 10), (5, 0, 10), (6, 0, 20)
, (7, 1, 30);
WITH
sumcte AS (
SELECT no, flag
, sum(CASE WHEN flag = 0 THEN amt ELSE -amt END) OVER (ORDER BY no) AS RsSum
FROM input)
, avgcte AS (
SELECT no, flag, RsSum
, cast(RsSum AS REAL) / count(no) FILTER (WHERE flag = 0)
OVER (ORDER BY no) As RsAvg
FROM sumcte)
SELECT RsSum
, (CASE WHEN flag = 0 THEN RsAvg
ELSE lag(RsAvg, 1) OVER (ORDER BY no) END) As RsAvg
FROM avgcte ORDER BY no;
produces
RsSum RsAvg
---------- ----------
10 10.0
30 15.0
60 20.0
50 20.0
60 15.0
80 16.0
50 16.0
(Interpreting RsAvg as that row's RsSum divided by the number of rows seen so far where flag is 0 is the only way I could make the numbers work.)
This will break when two rows in a row have a flag of 1, though. I'll update again if I figure a workaround for that case.
answered Nov 23 '18 at 9:49
ShawnShawn
4,0971613
edited Nov 23 '18 at 10:37
answered Nov 23 '18 at 9:49
ShawnShawn
4,0971613
answered Nov 23 '18 at 9:49
ShawnShawn
4,0971613
answered Nov 23 '18 at 9:49
ShawnShawn
4,0971613
4,0971613
Your answer was a big help. thank you.
– dinggu
Nov 23 '18 at 15:18
add a comment |
Your answer was a big help. thank you.
– dinggu
Nov 23 '18 at 15:18
Your answer was a big help. thank you.
– dinggu
Nov 23 '18 at 15:18
Your answer was a big help. thank you.
– dinggu
Nov 23 '18 at 15:18
add a comment |
You can filter the entities based on the condition (flag == 0) using some NSPredicate kind of function.
Then you can add the values of the sequence (sum1) using the reduce(_:_:) function.
Now that you have the sum1, you can similarly filter the (flag == 1) entities.
Use reduce function again on the sequence to get their sum2. Subtract sum2 from sum.
answered Nov 23 '18 at 9:32
DamonDamon
520318
add a comment |
You can filter the entities based on the condition (flag == 0) using some NSPredicate kind of function.
Then you can add the values of the sequence (sum1) using the reduce(_:_:) function.
Now that you have the sum1, you can similarly filter the (flag == 1) entities.
Use reduce function again on the sequence to get their sum2. Subtract sum2 from sum.
answered Nov 23 '18 at 9:32
DamonDamon
520318
add a comment |
You can filter the entities based on the condition (flag == 0) using some NSPredicate kind of function.
Then you can add the values of the sequence (sum1) using the reduce(_:_:) function.
Now that you have the sum1, you can similarly filter the (flag == 1) entities.
Use reduce function again on the sequence to get their sum2. Subtract sum2 from sum.
answered Nov 23 '18 at 9:32
DamonDamon
520318
You can filter the entities based on the condition (flag == 0) using some NSPredicate kind of function.
Then you can add the values of the sequence (sum1) using the reduce(_:_:) function.
Now that you have the sum1, you can similarly filter the (flag == 1) entities.
Use reduce function again on the sequence to get their sum2. Subtract sum2 from sum.
answered Nov 23 '18 at 9:32
DamonDamon
520318
answered Nov 23 '18 at 9:32
DamonDamon
520318
answered Nov 23 '18 at 9:32
DamonDamon
520318
answered Nov 23 '18 at 9:32
DamonDamon
520318
520318
add a comment |
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53443766%2fhow-do-i-calculate-the-sum-of-column-when-there-is-a-condition-in-sqlite-swift%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
I don't understand your logic. Can you explain it better?
– Tim Biegeleisen
Nov 23 '18 at 9:24