Summarizing data without dupes
0
I have some claims data from providers with dupes. My dupes are based on combining multiple columns (member_id + provider_id + claimDate + CPT). I would like to summarize the data by State and by CPT and sum the paid amt for unique claims only. Here is the data table and the desired result:
Here is my shot at this:
SELECT
State
,CPT
,SUM(paid)
FROM
Claims
GROUP BY
FS
,CPT;
Unfortunately, this sums all the dupe rows as well. How do I exclude the dupe rows when summarizing by State and by CPT?
Thanks
sql
edited Nov 16 '18 at 23:19
Eric Brandt
2,4151924
asked Nov 16 '18 at 21:52
DarumDarum
1
add a comment |
0
I have some claims data from providers with dupes. My dupes are based on combining multiple columns (member_id + provider_id + claimDate + CPT). I would like to summarize the data by State and by CPT and sum the paid amt for unique claims only. Here is the data table and the desired result:
Here is my shot at this:
SELECT
State
,CPT
,SUM(paid)
FROM
Claims
GROUP BY
FS
,CPT;
Unfortunately, this sums all the dupe rows as well. How do I exclude the dupe rows when summarizing by State and by CPT?
Thanks
sql
edited Nov 16 '18 at 23:19
Eric Brandt
2,4151924
asked Nov 16 '18 at 21:52
DarumDarum
1
You can have a sub query thats a self join and checks for count =1, and them sum in the outer query
– Doug Coats
Nov 16 '18 at 22:04
What isFSfield? There's no such field in your table.
– JohnyL
Nov 17 '18 at 11:02
By the way, your result table is incorrect because it's missing third row.
– JohnyL
Nov 17 '18 at 11:11
add a comment |
0
0
0
I have some claims data from providers with dupes. My dupes are based on combining multiple columns (member_id + provider_id + claimDate + CPT). I would like to summarize the data by State and by CPT and sum the paid amt for unique claims only. Here is the data table and the desired result:
Here is my shot at this:
SELECT
State
,CPT
,SUM(paid)
FROM
Claims
GROUP BY
FS
,CPT;
Unfortunately, this sums all the dupe rows as well. How do I exclude the dupe rows when summarizing by State and by CPT?
Thanks
sql
edited Nov 16 '18 at 23:19
Eric Brandt
2,4151924
asked Nov 16 '18 at 21:52
DarumDarum
1
I have some claims data from providers with dupes. My dupes are based on combining multiple columns (member_id + provider_id + claimDate + CPT). I would like to summarize the data by State and by CPT and sum the paid amt for unique claims only. Here is the data table and the desired result:
Here is my shot at this:
SELECT
State
,CPT
,SUM(paid)
FROM
Claims
GROUP BY
FS
,CPT;
Unfortunately, this sums all the dupe rows as well. How do I exclude the dupe rows when summarizing by State and by CPT?
Thanks
sql
sql
edited Nov 16 '18 at 23:19
Eric Brandt
2,4151924
asked Nov 16 '18 at 21:52
DarumDarum
1
edited Nov 16 '18 at 23:19
Eric Brandt
2,4151924
asked Nov 16 '18 at 21:52
DarumDarum
1
edited Nov 16 '18 at 23:19
Eric Brandt
2,4151924
edited Nov 16 '18 at 23:19
Eric Brandt
2,4151924
edited Nov 16 '18 at 23:19
Eric Brandt
2,4151924
2,4151924
asked Nov 16 '18 at 21:52
DarumDarum
1
asked Nov 16 '18 at 21:52
DarumDarum
1
asked Nov 16 '18 at 21:52
DarumDarum
1
1
You can have a sub query thats a self join and checks for count =1, and them sum in the outer query
– Doug Coats
Nov 16 '18 at 22:04
What isFSfield? There's no such field in your table.
– JohnyL
Nov 17 '18 at 11:02
By the way, your result table is incorrect because it's missing third row.
– JohnyL
Nov 17 '18 at 11:11
add a comment |
You can have a sub query thats a self join and checks for count =1, and them sum in the outer query
– Doug Coats
Nov 16 '18 at 22:04
What isFSfield? There's no such field in your table.
– JohnyL
Nov 17 '18 at 11:02
By the way, your result table is incorrect because it's missing third row.
– JohnyL
Nov 17 '18 at 11:11
You can have a sub query thats a self join and checks for count =1, and them sum in the outer query
– Doug Coats
Nov 16 '18 at 22:04
You can have a sub query thats a self join and checks for count =1, and them sum in the outer query
– Doug Coats
Nov 16 '18 at 22:04
What is
FS field? There's no such field in your table.– JohnyL
Nov 17 '18 at 11:02
What is
FS field? There's no such field in your table.– JohnyL
Nov 17 '18 at 11:02
By the way, your result table is incorrect because it's missing third row.
– JohnyL
Nov 17 '18 at 11:11
By the way, your result table is incorrect because it's missing third row.
– JohnyL
Nov 17 '18 at 11:11
add a comment |
2 Answers
2
active
oldest
votes
1
First deduplicate (Select Distinct), then group over the results from that:
Select State, CPT, sum(paid)
From ( Select Distinct member_id, provider_id, claimdate, State, CPT, paid From Claims)
Group By State, CPT
Note that this will only work if the amounts for the two claims are also the same, otherwise they will be counted as two.
answered Nov 16 '18 at 21:59
GolezTrolGolezTrol
98.1k9130174
How would I change your from statement if it's joint statement. Here is my From Statement: From ((Claims inner join ClaimDetails ON Claims.ClmID =ClaimDetails.ClmID) left join Rate ON (ClaimDetails.CR = Rate.CR) AND (ClaimDetails.CPT = Rate.CPT)) Left Join Claims2 on ClaimDetails.ClmID = Claims2.ClmID
– Darum
Nov 16 '18 at 22:36
1
You forgot claimdate
– Caius Jard
Nov 16 '18 at 22:51
add a comment |
0
declare @claims table
(
member_id int ,
provider_id varchar(10),
claimDate date,
CPT varchar(10),
[State] char(2),
Paid numeric
);
insert into @claims values
(123, 'abc', '2018-01-01', 'D11', 'NY', 10),
(123, 'abc', '2018-01-01', 'D11', 'NY', 10),
(123, 'abc', '2018-01-02', 'D11', 'NY', 5),
(555, 'xyz', '2018-01-05', '01T', 'NJ', 39),
(555, 'xyz', '2018-01-05', '01T', 'NJ', 39),
(666, 'xyz', '2018-01-05', 'V93', 'NJ', 11);
select [State], CPT, TheSum
from
(
select *,
RowNum = row_number() over (partition by member_id, provider_id, claimDate, CPT
order by member_id),
TheSum = sum(Paid) over (partition by member_id, provider_id, claimDate, CPT
order by member_id)
from @claims
) x
where x.RowNum = 1;
answered Nov 17 '18 at 11:09
JohnyLJohnyL
3,5801923
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%2f53345924%2fsummarizing-data-without-dupes%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
1
First deduplicate (Select Distinct), then group over the results from that:
Select State, CPT, sum(paid)
From ( Select Distinct member_id, provider_id, claimdate, State, CPT, paid From Claims)
Group By State, CPT
Note that this will only work if the amounts for the two claims are also the same, otherwise they will be counted as two.
answered Nov 16 '18 at 21:59
GolezTrolGolezTrol
98.1k9130174
How would I change your from statement if it's joint statement. Here is my From Statement: From ((Claims inner join ClaimDetails ON Claims.ClmID =ClaimDetails.ClmID) left join Rate ON (ClaimDetails.CR = Rate.CR) AND (ClaimDetails.CPT = Rate.CPT)) Left Join Claims2 on ClaimDetails.ClmID = Claims2.ClmID
– Darum
Nov 16 '18 at 22:36
1
You forgot claimdate
– Caius Jard
Nov 16 '18 at 22:51
add a comment |
1
First deduplicate (Select Distinct), then group over the results from that:
Select State, CPT, sum(paid)
From ( Select Distinct member_id, provider_id, claimdate, State, CPT, paid From Claims)
Group By State, CPT
Note that this will only work if the amounts for the two claims are also the same, otherwise they will be counted as two.
answered Nov 16 '18 at 21:59
GolezTrolGolezTrol
98.1k9130174
How would I change your from statement if it's joint statement. Here is my From Statement: From ((Claims inner join ClaimDetails ON Claims.ClmID =ClaimDetails.ClmID) left join Rate ON (ClaimDetails.CR = Rate.CR) AND (ClaimDetails.CPT = Rate.CPT)) Left Join Claims2 on ClaimDetails.ClmID = Claims2.ClmID
– Darum
Nov 16 '18 at 22:36
1
You forgot claimdate
– Caius Jard
Nov 16 '18 at 22:51
add a comment |
1
1
1
First deduplicate (Select Distinct), then group over the results from that:
Select State, CPT, sum(paid)
From ( Select Distinct member_id, provider_id, claimdate, State, CPT, paid From Claims)
Group By State, CPT
Note that this will only work if the amounts for the two claims are also the same, otherwise they will be counted as two.
answered Nov 16 '18 at 21:59
GolezTrolGolezTrol
98.1k9130174
First deduplicate (Select Distinct), then group over the results from that:
Select State, CPT, sum(paid)
From ( Select Distinct member_id, provider_id, claimdate, State, CPT, paid From Claims)
Group By State, CPT
Note that this will only work if the amounts for the two claims are also the same, otherwise they will be counted as two.
answered Nov 16 '18 at 21:59
GolezTrolGolezTrol
98.1k9130174
edited Nov 17 '18 at 10:04
answered Nov 16 '18 at 21:59
GolezTrolGolezTrol
98.1k9130174
answered Nov 16 '18 at 21:59
GolezTrolGolezTrol
98.1k9130174
answered Nov 16 '18 at 21:59
GolezTrolGolezTrol
98.1k9130174
98.1k9130174
How would I change your from statement if it's joint statement. Here is my From Statement: From ((Claims inner join ClaimDetails ON Claims.ClmID =ClaimDetails.ClmID) left join Rate ON (ClaimDetails.CR = Rate.CR) AND (ClaimDetails.CPT = Rate.CPT)) Left Join Claims2 on ClaimDetails.ClmID = Claims2.ClmID
– Darum
Nov 16 '18 at 22:36
1
You forgot claimdate
– Caius Jard
Nov 16 '18 at 22:51
add a comment |
How would I change your from statement if it's joint statement. Here is my From Statement: From ((Claims inner join ClaimDetails ON Claims.ClmID =ClaimDetails.ClmID) left join Rate ON (ClaimDetails.CR = Rate.CR) AND (ClaimDetails.CPT = Rate.CPT)) Left Join Claims2 on ClaimDetails.ClmID = Claims2.ClmID
– Darum
Nov 16 '18 at 22:36
1
You forgot claimdate
– Caius Jard
Nov 16 '18 at 22:51
How would I change your from statement if it's joint statement. Here is my From Statement: From ((Claims inner join ClaimDetails ON Claims.ClmID =ClaimDetails.ClmID) left join Rate ON (ClaimDetails.CR = Rate.CR) AND (ClaimDetails.CPT = Rate.CPT)) Left Join Claims2 on ClaimDetails.ClmID = Claims2.ClmID
– Darum
Nov 16 '18 at 22:36
How would I change your from statement if it's joint statement. Here is my From Statement: From ((Claims inner join ClaimDetails ON Claims.ClmID =ClaimDetails.ClmID) left join Rate ON (ClaimDetails.CR = Rate.CR) AND (ClaimDetails.CPT = Rate.CPT)) Left Join Claims2 on ClaimDetails.ClmID = Claims2.ClmID
– Darum
Nov 16 '18 at 22:36
1
1
You forgot claimdate
– Caius Jard
Nov 16 '18 at 22:51
You forgot claimdate
– Caius Jard
Nov 16 '18 at 22:51
add a comment |
0
declare @claims table
(
member_id int ,
provider_id varchar(10),
claimDate date,
CPT varchar(10),
[State] char(2),
Paid numeric
);
insert into @claims values
(123, 'abc', '2018-01-01', 'D11', 'NY', 10),
(123, 'abc', '2018-01-01', 'D11', 'NY', 10),
(123, 'abc', '2018-01-02', 'D11', 'NY', 5),
(555, 'xyz', '2018-01-05', '01T', 'NJ', 39),
(555, 'xyz', '2018-01-05', '01T', 'NJ', 39),
(666, 'xyz', '2018-01-05', 'V93', 'NJ', 11);
select [State], CPT, TheSum
from
(
select *,
RowNum = row_number() over (partition by member_id, provider_id, claimDate, CPT
order by member_id),
TheSum = sum(Paid) over (partition by member_id, provider_id, claimDate, CPT
order by member_id)
from @claims
) x
where x.RowNum = 1;
answered Nov 17 '18 at 11:09
JohnyLJohnyL
3,5801923
add a comment |
0
declare @claims table
(
member_id int ,
provider_id varchar(10),
claimDate date,
CPT varchar(10),
[State] char(2),
Paid numeric
);
insert into @claims values
(123, 'abc', '2018-01-01', 'D11', 'NY', 10),
(123, 'abc', '2018-01-01', 'D11', 'NY', 10),
(123, 'abc', '2018-01-02', 'D11', 'NY', 5),
(555, 'xyz', '2018-01-05', '01T', 'NJ', 39),
(555, 'xyz', '2018-01-05', '01T', 'NJ', 39),
(666, 'xyz', '2018-01-05', 'V93', 'NJ', 11);
select [State], CPT, TheSum
from
(
select *,
RowNum = row_number() over (partition by member_id, provider_id, claimDate, CPT
order by member_id),
TheSum = sum(Paid) over (partition by member_id, provider_id, claimDate, CPT
order by member_id)
from @claims
) x
where x.RowNum = 1;
answered Nov 17 '18 at 11:09
JohnyLJohnyL
3,5801923
add a comment |
0
0
0
declare @claims table
(
member_id int ,
provider_id varchar(10),
claimDate date,
CPT varchar(10),
[State] char(2),
Paid numeric
);
insert into @claims values
(123, 'abc', '2018-01-01', 'D11', 'NY', 10),
(123, 'abc', '2018-01-01', 'D11', 'NY', 10),
(123, 'abc', '2018-01-02', 'D11', 'NY', 5),
(555, 'xyz', '2018-01-05', '01T', 'NJ', 39),
(555, 'xyz', '2018-01-05', '01T', 'NJ', 39),
(666, 'xyz', '2018-01-05', 'V93', 'NJ', 11);
select [State], CPT, TheSum
from
(
select *,
RowNum = row_number() over (partition by member_id, provider_id, claimDate, CPT
order by member_id),
TheSum = sum(Paid) over (partition by member_id, provider_id, claimDate, CPT
order by member_id)
from @claims
) x
where x.RowNum = 1;
answered Nov 17 '18 at 11:09
JohnyLJohnyL
3,5801923
declare @claims table
(
member_id int ,
provider_id varchar(10),
claimDate date,
CPT varchar(10),
[State] char(2),
Paid numeric
);
insert into @claims values
(123, 'abc', '2018-01-01', 'D11', 'NY', 10),
(123, 'abc', '2018-01-01', 'D11', 'NY', 10),
(123, 'abc', '2018-01-02', 'D11', 'NY', 5),
(555, 'xyz', '2018-01-05', '01T', 'NJ', 39),
(555, 'xyz', '2018-01-05', '01T', 'NJ', 39),
(666, 'xyz', '2018-01-05', 'V93', 'NJ', 11);
select [State], CPT, TheSum
from
(
select *,
RowNum = row_number() over (partition by member_id, provider_id, claimDate, CPT
order by member_id),
TheSum = sum(Paid) over (partition by member_id, provider_id, claimDate, CPT
order by member_id)
from @claims
) x
where x.RowNum = 1;
answered Nov 17 '18 at 11:09
JohnyLJohnyL
3,5801923
edited Nov 17 '18 at 11:16
answered Nov 17 '18 at 11:09
JohnyLJohnyL
3,5801923
answered Nov 17 '18 at 11:09
JohnyLJohnyL
3,5801923
answered Nov 17 '18 at 11:09
JohnyLJohnyL
3,5801923
3,5801923
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%2f53345924%2fsummarizing-data-without-dupes%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
You can have a sub query thats a self join and checks for count =1, and them sum in the outer query
– Doug Coats
Nov 16 '18 at 22:04
What is
FSfield? There's no such field in your table.– JohnyL
Nov 17 '18 at 11:02
By the way, your result table is incorrect because it's missing third row.
– JohnyL
Nov 17 '18 at 11:11