T-SQL - Running total per project
2
I have this simplified table structure:
I need to generate an aggregate result, sum'ing the transactions per project/period - and have a running total per project. I added the column TransactionCountInPeriod
How can I write a query to get a result like this:
This is as far as I got with the query... I cannot figure out how to do the running total per ProjectId.
SELECT
prj.Id AS ProjectId,
p.Id AS PeriodId,
SUM(t.Amount) AS SumInPeriod--,
--SUM(t.Amount) OVER (PARTITION BY prj.Id) AS RunningTotal
--COUNT(t.*) AS TransactionCountInPeriod
FROM
Periods p
CROSS JOIN Projects prj
LEFT OUTER JOIN Transactions t ON p.Id = t.PeriodId
GROUP BY
prj.Id,
p.Id
ORDER BY
prj.Id,
p.Id
Schema and Testdata
CREATE TABLE [dbo].[Periods](
[Id] [int] NOT NULL
)
GO
CREATE TABLE [dbo].[Projects](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL
)
GO
CREATE TABLE [dbo].[Transactions](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ProjectId] [int] NOT NULL,
[PeriodId] [int] NOT NULL,
[Amount] [decimal](18, 0) NOT NULL
)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201801)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201802)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201803)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201804)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201805)
GO
SET IDENTITY_INSERT [dbo].[Projects] ON
GO
INSERT [dbo].[Projects] ([Id], [Name]) VALUES (1, N'Alpha')
GO
INSERT [dbo].[Projects] ([Id], [Name]) VALUES (2, N'Beta')
GO
SET IDENTITY_INSERT [dbo].[Projects] OFF
GO
SET IDENTITY_INSERT [dbo].[Transactions] ON
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (1, 1, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (2, 1, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (3, 1, 201802, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (4, 1, 201803, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (5, 1, 201803, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (6, 1, 201804, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (7, 2, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (8, 2, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (12, 2, 201804, CAST(100 AS Decimal(18, 0)))
GO
SET IDENTITY_INSERT [dbo].[Transactions] OFF
GO
(I would have created a sql fiddle, but it keeps crashing when I try...)
tsql
sql-server-2017 asked Nov 28 '18 at 12:21
KjensenKjensen
4,8733085141
add a comment |
2
I have this simplified table structure:
I need to generate an aggregate result, sum'ing the transactions per project/period - and have a running total per project. I added the column TransactionCountInPeriod
How can I write a query to get a result like this:
This is as far as I got with the query... I cannot figure out how to do the running total per ProjectId.
SELECT
prj.Id AS ProjectId,
p.Id AS PeriodId,
SUM(t.Amount) AS SumInPeriod--,
--SUM(t.Amount) OVER (PARTITION BY prj.Id) AS RunningTotal
--COUNT(t.*) AS TransactionCountInPeriod
FROM
Periods p
CROSS JOIN Projects prj
LEFT OUTER JOIN Transactions t ON p.Id = t.PeriodId
GROUP BY
prj.Id,
p.Id
ORDER BY
prj.Id,
p.Id
Schema and Testdata
CREATE TABLE [dbo].[Periods](
[Id] [int] NOT NULL
)
GO
CREATE TABLE [dbo].[Projects](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL
)
GO
CREATE TABLE [dbo].[Transactions](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ProjectId] [int] NOT NULL,
[PeriodId] [int] NOT NULL,
[Amount] [decimal](18, 0) NOT NULL
)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201801)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201802)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201803)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201804)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201805)
GO
SET IDENTITY_INSERT [dbo].[Projects] ON
GO
INSERT [dbo].[Projects] ([Id], [Name]) VALUES (1, N'Alpha')
GO
INSERT [dbo].[Projects] ([Id], [Name]) VALUES (2, N'Beta')
GO
SET IDENTITY_INSERT [dbo].[Projects] OFF
GO
SET IDENTITY_INSERT [dbo].[Transactions] ON
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (1, 1, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (2, 1, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (3, 1, 201802, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (4, 1, 201803, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (5, 1, 201803, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (6, 1, 201804, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (7, 2, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (8, 2, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (12, 2, 201804, CAST(100 AS Decimal(18, 0)))
GO
SET IDENTITY_INSERT [dbo].[Transactions] OFF
GO
(I would have created a sql fiddle, but it keeps crashing when I try...)
tsql
sql-server-2017 asked Nov 28 '18 at 12:21
KjensenKjensen
4,8733085141
add a comment |
2
2
2
I have this simplified table structure:
I need to generate an aggregate result, sum'ing the transactions per project/period - and have a running total per project. I added the column TransactionCountInPeriod
How can I write a query to get a result like this:
This is as far as I got with the query... I cannot figure out how to do the running total per ProjectId.
SELECT
prj.Id AS ProjectId,
p.Id AS PeriodId,
SUM(t.Amount) AS SumInPeriod--,
--SUM(t.Amount) OVER (PARTITION BY prj.Id) AS RunningTotal
--COUNT(t.*) AS TransactionCountInPeriod
FROM
Periods p
CROSS JOIN Projects prj
LEFT OUTER JOIN Transactions t ON p.Id = t.PeriodId
GROUP BY
prj.Id,
p.Id
ORDER BY
prj.Id,
p.Id
Schema and Testdata
CREATE TABLE [dbo].[Periods](
[Id] [int] NOT NULL
)
GO
CREATE TABLE [dbo].[Projects](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL
)
GO
CREATE TABLE [dbo].[Transactions](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ProjectId] [int] NOT NULL,
[PeriodId] [int] NOT NULL,
[Amount] [decimal](18, 0) NOT NULL
)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201801)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201802)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201803)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201804)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201805)
GO
SET IDENTITY_INSERT [dbo].[Projects] ON
GO
INSERT [dbo].[Projects] ([Id], [Name]) VALUES (1, N'Alpha')
GO
INSERT [dbo].[Projects] ([Id], [Name]) VALUES (2, N'Beta')
GO
SET IDENTITY_INSERT [dbo].[Projects] OFF
GO
SET IDENTITY_INSERT [dbo].[Transactions] ON
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (1, 1, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (2, 1, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (3, 1, 201802, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (4, 1, 201803, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (5, 1, 201803, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (6, 1, 201804, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (7, 2, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (8, 2, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (12, 2, 201804, CAST(100 AS Decimal(18, 0)))
GO
SET IDENTITY_INSERT [dbo].[Transactions] OFF
GO
(I would have created a sql fiddle, but it keeps crashing when I try...)
tsql
sql-server-2017 asked Nov 28 '18 at 12:21
KjensenKjensen
4,8733085141
I have this simplified table structure:
I need to generate an aggregate result, sum'ing the transactions per project/period - and have a running total per project. I added the column TransactionCountInPeriod
How can I write a query to get a result like this:
This is as far as I got with the query... I cannot figure out how to do the running total per ProjectId.
SELECT
prj.Id AS ProjectId,
p.Id AS PeriodId,
SUM(t.Amount) AS SumInPeriod--,
--SUM(t.Amount) OVER (PARTITION BY prj.Id) AS RunningTotal
--COUNT(t.*) AS TransactionCountInPeriod
FROM
Periods p
CROSS JOIN Projects prj
LEFT OUTER JOIN Transactions t ON p.Id = t.PeriodId
GROUP BY
prj.Id,
p.Id
ORDER BY
prj.Id,
p.Id
Schema and Testdata
CREATE TABLE [dbo].[Periods](
[Id] [int] NOT NULL
)
GO
CREATE TABLE [dbo].[Projects](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL
)
GO
CREATE TABLE [dbo].[Transactions](
[Id] [int] IDENTITY(1,1) NOT NULL,
[ProjectId] [int] NOT NULL,
[PeriodId] [int] NOT NULL,
[Amount] [decimal](18, 0) NOT NULL
)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201801)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201802)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201803)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201804)
GO
INSERT [dbo].[Periods] ([Id]) VALUES (201805)
GO
SET IDENTITY_INSERT [dbo].[Projects] ON
GO
INSERT [dbo].[Projects] ([Id], [Name]) VALUES (1, N'Alpha')
GO
INSERT [dbo].[Projects] ([Id], [Name]) VALUES (2, N'Beta')
GO
SET IDENTITY_INSERT [dbo].[Projects] OFF
GO
SET IDENTITY_INSERT [dbo].[Transactions] ON
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (1, 1, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (2, 1, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (3, 1, 201802, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (4, 1, 201803, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (5, 1, 201803, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (6, 1, 201804, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (7, 2, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (8, 2, 201801, CAST(100 AS Decimal(18, 0)))
GO
INSERT [dbo].[Transactions] ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (12, 2, 201804, CAST(100 AS Decimal(18, 0)))
GO
SET IDENTITY_INSERT [dbo].[Transactions] OFF
GO
(I would have created a sql fiddle, but it keeps crashing when I try...)
tsql
sql-server-2017 tsql
sql-server-2017 asked Nov 28 '18 at 12:21
KjensenKjensen
4,8733085141
asked Nov 28 '18 at 12:21
KjensenKjensen
4,8733085141
edited Nov 28 '18 at 14:31
Kjensen
asked Nov 28 '18 at 12:21
KjensenKjensen
4,8733085141
asked Nov 28 '18 at 12:21
KjensenKjensen
4,8733085141
asked Nov 28 '18 at 12:21
KjensenKjensen
4,8733085141
4,8733085141
add a comment |
add a comment |
2 Answers
2
active
oldest
votes
3
You can use the SUM window function to calculate the running total.
DECLARE @Periods TABLE(
[Id] [int] NOT NULL
)
DECLARE @Projects TABLE(
[Id] [int] NOT NULL,
[Name] [varchar](50) NOT NULL
)
DECLARE @Transactions TABLE(
[Id] [int] NOT NULL,
[ProjectId] [int] NOT NULL,
[PeriodId] [int] NOT NULL,
[Amount] [decimal](18, 0) NOT NULL
)
INSERT @Periods ([Id]) VALUES (201801)
INSERT @Periods ([Id]) VALUES (201802)
INSERT @Periods ([Id]) VALUES (201803)
INSERT @Periods ([Id]) VALUES (201804)
INSERT @Periods ([Id]) VALUES (201805)
INSERT @Projects ([Id], [Name]) VALUES (1, N'Alpha')
INSERT @Projects ([Id], [Name]) VALUES (2, N'Beta')
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (1, 1, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (2, 1, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (3, 1, 201802, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (4, 1, 201803, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (5, 1, 201803, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (6, 1, 201804, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (7, 2, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (8, 2, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (12, 2, 201804, CAST(100 AS Decimal(18, 0)))
select
pr.Id as ProjectId
, pe.Id as PeriodId
, isnull(tr.SumAmount, 0) as SumInPeriod
, isnull(sum(tr.SumAmount) over(partition by pr.Id order by pe.Id), 0) as RunningTotal
, tr.TransactionsCount as TransactionCountInPeriod
from @Projects pr
cross join @Periods pe
outer apply (select sum(t.Amount) as SumAmount, count(*) as TransactionsCount from @Transactions t where t.ProjectId = pr.Id and t.PeriodId = pe.Id) tr
order by ProjectId, PeriodId
answered Nov 28 '18 at 12:43
Andrey NikolovAndrey Nikolov
4,3233922
add a comment |
2
The simplest option would be to use sum...over.
You where very close - but you don't need the group by and you do need to add order by to the over clause to get a running total - like this:
SELECT pr.Id As ProjectId,
pe.Id As PeriodId,
SUM(Amount) OVER(PARTITION BY pe.Id, pr.Id) AS SumInPeriod,
SUM(Amount) OVER(PARTITION BY pr.Id ORDER BY pe.Id) AS RunningTotal,
COUNT(Amount) OVER(PARTITION BY pe.Id, pr.Id) TransactionCountInPeriod
FROM (
Periods pe
CROSS JOIN projects as pr
)
LEFT JOIN Transactions tr
ON pe.Id = tr.PeriodId
AND tr.ProjectId = pr.Id
ORDER BY pr.id, pe.id
answered Nov 28 '18 at 12:44
Zohar PeledZohar Peled
55.9k73474
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
});
}
});
draft saved
draft discarded
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%2f53519364%2ft-sql-running-total-per-project%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
3
You can use the SUM window function to calculate the running total.
DECLARE @Periods TABLE(
[Id] [int] NOT NULL
)
DECLARE @Projects TABLE(
[Id] [int] NOT NULL,
[Name] [varchar](50) NOT NULL
)
DECLARE @Transactions TABLE(
[Id] [int] NOT NULL,
[ProjectId] [int] NOT NULL,
[PeriodId] [int] NOT NULL,
[Amount] [decimal](18, 0) NOT NULL
)
INSERT @Periods ([Id]) VALUES (201801)
INSERT @Periods ([Id]) VALUES (201802)
INSERT @Periods ([Id]) VALUES (201803)
INSERT @Periods ([Id]) VALUES (201804)
INSERT @Periods ([Id]) VALUES (201805)
INSERT @Projects ([Id], [Name]) VALUES (1, N'Alpha')
INSERT @Projects ([Id], [Name]) VALUES (2, N'Beta')
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (1, 1, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (2, 1, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (3, 1, 201802, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (4, 1, 201803, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (5, 1, 201803, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (6, 1, 201804, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (7, 2, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (8, 2, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (12, 2, 201804, CAST(100 AS Decimal(18, 0)))
select
pr.Id as ProjectId
, pe.Id as PeriodId
, isnull(tr.SumAmount, 0) as SumInPeriod
, isnull(sum(tr.SumAmount) over(partition by pr.Id order by pe.Id), 0) as RunningTotal
, tr.TransactionsCount as TransactionCountInPeriod
from @Projects pr
cross join @Periods pe
outer apply (select sum(t.Amount) as SumAmount, count(*) as TransactionsCount from @Transactions t where t.ProjectId = pr.Id and t.PeriodId = pe.Id) tr
order by ProjectId, PeriodId
answered Nov 28 '18 at 12:43
Andrey NikolovAndrey Nikolov
4,3233922
add a comment |
3
You can use the SUM window function to calculate the running total.
DECLARE @Periods TABLE(
[Id] [int] NOT NULL
)
DECLARE @Projects TABLE(
[Id] [int] NOT NULL,
[Name] [varchar](50) NOT NULL
)
DECLARE @Transactions TABLE(
[Id] [int] NOT NULL,
[ProjectId] [int] NOT NULL,
[PeriodId] [int] NOT NULL,
[Amount] [decimal](18, 0) NOT NULL
)
INSERT @Periods ([Id]) VALUES (201801)
INSERT @Periods ([Id]) VALUES (201802)
INSERT @Periods ([Id]) VALUES (201803)
INSERT @Periods ([Id]) VALUES (201804)
INSERT @Periods ([Id]) VALUES (201805)
INSERT @Projects ([Id], [Name]) VALUES (1, N'Alpha')
INSERT @Projects ([Id], [Name]) VALUES (2, N'Beta')
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (1, 1, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (2, 1, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (3, 1, 201802, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (4, 1, 201803, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (5, 1, 201803, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (6, 1, 201804, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (7, 2, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (8, 2, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (12, 2, 201804, CAST(100 AS Decimal(18, 0)))
select
pr.Id as ProjectId
, pe.Id as PeriodId
, isnull(tr.SumAmount, 0) as SumInPeriod
, isnull(sum(tr.SumAmount) over(partition by pr.Id order by pe.Id), 0) as RunningTotal
, tr.TransactionsCount as TransactionCountInPeriod
from @Projects pr
cross join @Periods pe
outer apply (select sum(t.Amount) as SumAmount, count(*) as TransactionsCount from @Transactions t where t.ProjectId = pr.Id and t.PeriodId = pe.Id) tr
order by ProjectId, PeriodId
answered Nov 28 '18 at 12:43
Andrey NikolovAndrey Nikolov
4,3233922
add a comment |
3
3
3
You can use the SUM window function to calculate the running total.
DECLARE @Periods TABLE(
[Id] [int] NOT NULL
)
DECLARE @Projects TABLE(
[Id] [int] NOT NULL,
[Name] [varchar](50) NOT NULL
)
DECLARE @Transactions TABLE(
[Id] [int] NOT NULL,
[ProjectId] [int] NOT NULL,
[PeriodId] [int] NOT NULL,
[Amount] [decimal](18, 0) NOT NULL
)
INSERT @Periods ([Id]) VALUES (201801)
INSERT @Periods ([Id]) VALUES (201802)
INSERT @Periods ([Id]) VALUES (201803)
INSERT @Periods ([Id]) VALUES (201804)
INSERT @Periods ([Id]) VALUES (201805)
INSERT @Projects ([Id], [Name]) VALUES (1, N'Alpha')
INSERT @Projects ([Id], [Name]) VALUES (2, N'Beta')
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (1, 1, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (2, 1, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (3, 1, 201802, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (4, 1, 201803, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (5, 1, 201803, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (6, 1, 201804, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (7, 2, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (8, 2, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (12, 2, 201804, CAST(100 AS Decimal(18, 0)))
select
pr.Id as ProjectId
, pe.Id as PeriodId
, isnull(tr.SumAmount, 0) as SumInPeriod
, isnull(sum(tr.SumAmount) over(partition by pr.Id order by pe.Id), 0) as RunningTotal
, tr.TransactionsCount as TransactionCountInPeriod
from @Projects pr
cross join @Periods pe
outer apply (select sum(t.Amount) as SumAmount, count(*) as TransactionsCount from @Transactions t where t.ProjectId = pr.Id and t.PeriodId = pe.Id) tr
order by ProjectId, PeriodId
answered Nov 28 '18 at 12:43
Andrey NikolovAndrey Nikolov
4,3233922
You can use the SUM window function to calculate the running total.
DECLARE @Periods TABLE(
[Id] [int] NOT NULL
)
DECLARE @Projects TABLE(
[Id] [int] NOT NULL,
[Name] [varchar](50) NOT NULL
)
DECLARE @Transactions TABLE(
[Id] [int] NOT NULL,
[ProjectId] [int] NOT NULL,
[PeriodId] [int] NOT NULL,
[Amount] [decimal](18, 0) NOT NULL
)
INSERT @Periods ([Id]) VALUES (201801)
INSERT @Periods ([Id]) VALUES (201802)
INSERT @Periods ([Id]) VALUES (201803)
INSERT @Periods ([Id]) VALUES (201804)
INSERT @Periods ([Id]) VALUES (201805)
INSERT @Projects ([Id], [Name]) VALUES (1, N'Alpha')
INSERT @Projects ([Id], [Name]) VALUES (2, N'Beta')
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (1, 1, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (2, 1, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (3, 1, 201802, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (4, 1, 201803, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (5, 1, 201803, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (6, 1, 201804, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (7, 2, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (8, 2, 201801, CAST(100 AS Decimal(18, 0)))
INSERT @Transactions ([Id], [ProjectId], [PeriodId], [Amount]) VALUES (12, 2, 201804, CAST(100 AS Decimal(18, 0)))
select
pr.Id as ProjectId
, pe.Id as PeriodId
, isnull(tr.SumAmount, 0) as SumInPeriod
, isnull(sum(tr.SumAmount) over(partition by pr.Id order by pe.Id), 0) as RunningTotal
, tr.TransactionsCount as TransactionCountInPeriod
from @Projects pr
cross join @Periods pe
outer apply (select sum(t.Amount) as SumAmount, count(*) as TransactionsCount from @Transactions t where t.ProjectId = pr.Id and t.PeriodId = pe.Id) tr
order by ProjectId, PeriodId
answered Nov 28 '18 at 12:43
Andrey NikolovAndrey Nikolov
4,3233922
answered Nov 28 '18 at 12:43
Andrey NikolovAndrey Nikolov
4,3233922
answered Nov 28 '18 at 12:43
Andrey NikolovAndrey Nikolov
4,3233922
answered Nov 28 '18 at 12:43
Andrey NikolovAndrey Nikolov
4,3233922
4,3233922
add a comment |
add a comment |
2
The simplest option would be to use sum...over.
You where very close - but you don't need the group by and you do need to add order by to the over clause to get a running total - like this:
SELECT pr.Id As ProjectId,
pe.Id As PeriodId,
SUM(Amount) OVER(PARTITION BY pe.Id, pr.Id) AS SumInPeriod,
SUM(Amount) OVER(PARTITION BY pr.Id ORDER BY pe.Id) AS RunningTotal,
COUNT(Amount) OVER(PARTITION BY pe.Id, pr.Id) TransactionCountInPeriod
FROM (
Periods pe
CROSS JOIN projects as pr
)
LEFT JOIN Transactions tr
ON pe.Id = tr.PeriodId
AND tr.ProjectId = pr.Id
ORDER BY pr.id, pe.id
answered Nov 28 '18 at 12:44
Zohar PeledZohar Peled
55.9k73474
add a comment |
2
The simplest option would be to use sum...over.
You where very close - but you don't need the group by and you do need to add order by to the over clause to get a running total - like this:
SELECT pr.Id As ProjectId,
pe.Id As PeriodId,
SUM(Amount) OVER(PARTITION BY pe.Id, pr.Id) AS SumInPeriod,
SUM(Amount) OVER(PARTITION BY pr.Id ORDER BY pe.Id) AS RunningTotal,
COUNT(Amount) OVER(PARTITION BY pe.Id, pr.Id) TransactionCountInPeriod
FROM (
Periods pe
CROSS JOIN projects as pr
)
LEFT JOIN Transactions tr
ON pe.Id = tr.PeriodId
AND tr.ProjectId = pr.Id
ORDER BY pr.id, pe.id
answered Nov 28 '18 at 12:44
Zohar PeledZohar Peled
55.9k73474
add a comment |
2
2
2
The simplest option would be to use sum...over.
You where very close - but you don't need the group by and you do need to add order by to the over clause to get a running total - like this:
SELECT pr.Id As ProjectId,
pe.Id As PeriodId,
SUM(Amount) OVER(PARTITION BY pe.Id, pr.Id) AS SumInPeriod,
SUM(Amount) OVER(PARTITION BY pr.Id ORDER BY pe.Id) AS RunningTotal,
COUNT(Amount) OVER(PARTITION BY pe.Id, pr.Id) TransactionCountInPeriod
FROM (
Periods pe
CROSS JOIN projects as pr
)
LEFT JOIN Transactions tr
ON pe.Id = tr.PeriodId
AND tr.ProjectId = pr.Id
ORDER BY pr.id, pe.id
answered Nov 28 '18 at 12:44
Zohar PeledZohar Peled
55.9k73474
The simplest option would be to use sum...over.
You where very close - but you don't need the group by and you do need to add order by to the over clause to get a running total - like this:
SELECT pr.Id As ProjectId,
pe.Id As PeriodId,
SUM(Amount) OVER(PARTITION BY pe.Id, pr.Id) AS SumInPeriod,
SUM(Amount) OVER(PARTITION BY pr.Id ORDER BY pe.Id) AS RunningTotal,
COUNT(Amount) OVER(PARTITION BY pe.Id, pr.Id) TransactionCountInPeriod
FROM (
Periods pe
CROSS JOIN projects as pr
)
LEFT JOIN Transactions tr
ON pe.Id = tr.PeriodId
AND tr.ProjectId = pr.Id
ORDER BY pr.id, pe.id
answered Nov 28 '18 at 12:44
Zohar PeledZohar Peled
55.9k73474
answered Nov 28 '18 at 12:44
Zohar PeledZohar Peled
55.9k73474
answered Nov 28 '18 at 12:44
Zohar PeledZohar Peled
55.9k73474
answered Nov 28 '18 at 12:44
Zohar PeledZohar Peled
55.9k73474
55.9k73474
add a comment |
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53519364%2ft-sql-running-total-per-project%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
