Summarizing data without dupes





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







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:



enter image description here



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










share|improve this question

























  • 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











  • By the way, your result table is incorrect because it's missing third row.

    – JohnyL
    Nov 17 '18 at 11:11


















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:



enter image description here



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










share|improve this question

























  • 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











  • By the way, your result table is incorrect because it's missing third row.

    – JohnyL
    Nov 17 '18 at 11:11














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:



enter image description here



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










share|improve this question
















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:



enter image description here



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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 16 '18 at 23:19









Eric Brandt

3,30011127




3,30011127










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 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



















  • 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











  • 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












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.






share|improve this answer


























  • 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



















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;





share|improve this answer


























    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


















    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.






    share|improve this answer


























    • 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
















    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.






    share|improve this answer


























    • 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














    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.






    share|improve this answer















    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.







    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Nov 17 '18 at 10:04

























    answered Nov 16 '18 at 21:59









    GolezTrolGolezTrol

    100k10136176




    100k10136176













    • 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






    • 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













    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;





    share|improve this answer






























      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;





      share|improve this answer




























        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;





        share|improve this answer















        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;






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Nov 17 '18 at 11:16

























        answered Nov 17 '18 at 11:09









        JohnyLJohnyL

        3,73811025




        3,73811025






























            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














            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





















































            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







            Popular posts from this blog

            Bressuire

            Vorschmack

            Quarantine