How to sum across multiple rows for multiple fields

Question

Can anyone please advise on how to go about writing a SQL query to include the sum for multiple fields across multiple rows by group. I'm using the below query, but it keeps saying that the fields "in the select line are invalid because it is not contained in either an aggregate function or the GROUP BY clause."

    Select ClaimId,InternalICN,BilledAmt, 
           Sum(PayAmt) as TotPayAmt,Sum(COBAmt) as TotCOBAmt,Sum(PrePayAmt) as  
           TotPrePayAmt 
    from CAIDEnc.IntEncTracking.EncounterList
    where BypassFlag = 0 and
     BypassReason = 0
    group by ClaimId, InternalICN

Any advice would be greatly appreciated. Thanks!

the only field that is problematic is BilledAmt. What should this be? — Sparky
– Sparky, Commented Jul 11, 2013 at 18:45
Thanks, Sparky. I realized that BilledAmt should include an aggregate function or it needs to be included in the Group by clause. — daniness
– daniness, Commented Jul 11, 2013 at 19:56

Alejandro Colorado · Accepted Answer · 2013-07-11 18:48:06Z

1

Depending on what you really want, you have two options:

OPTION #1: Remove the BilledAmt from the SELECT

Select ClaimId,InternalICN, 
           Sum(PayAmt) as TotPayAmt,Sum(COBAmt) as TotCOBAmt,Sum(PrePayAmt) as  
           TotPrePayAmt 
    from CAIDEnc.IntEncTracking.EncounterList
    where BypassFlag = 0 and
     BypassReason = 0
    group by ClaimId, InternalICN

or

OPTION #2: Include the BilledAmt in the GROUP BY

Select ClaimId,InternalICN,BilledAmt, 
           Sum(PayAmt) as TotPayAmt,Sum(COBAmt) as TotCOBAmt,Sum(PrePayAmt) as  
           TotPrePayAmt 
    from CAIDEnc.IntEncTracking.EncounterList
    where BypassFlag = 0 and
     BypassReason = 0
    group by ClaimId, InternalICN,BilledAmt

answered Jul 11, 2013 at 18:48

Alejandro Colorado

6,0842 gold badges30 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

daniness Over a year ago

Thank you, this worked. So no field can be included in a Select statement like this, unless it is preceeded with an aggregate function, or in the Group By clause?

Paul Chernoch · Accepted Answer · 2013-07-11 18:45:35Z

1

BilledAmt is not in the group by clause. You must put it there, or aggregate it with a sum, average or other function.

answered Jul 11, 2013 at 18:45

Paul Chernoch

5,5933 gold badges61 silver badges78 bronze badges

Comments

Diceydawg · Accepted Answer · 2013-07-11 18:46:21Z

1

BilledAmt is not in a aggregate query. When you use group by you can only select aggregates or any field in the group by clause/

answered Jul 11, 2013 at 18:46

Diceydawg

191 silver badge3 bronze badges

1 Comment

Diceydawg Over a year ago

To clarify the reason why you cannot select BilledAmt is because when you group by the aggregates then there may be multiple values of BilledAmt within each group.

Collectives™ on Stack Overflow

How to sum across multiple rows for multiple fields

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related