Groups and Aggregate functions questions

Question

SELECT HCIS,ServiceDate,PrimeInsMnemonic,TIN,Amount,SUM(Amount) AS Total
FROM dbo.PbrChargeTransactions
WHERE HCIS = 'ASL'
AND ServiceDate Between'01/01/16' and '12/31/16'
AND PrimeInsMnemonic = 'MED'
AND TIN in ('460224598','46-0224598')
GROUP By HCIS
ORDER BY SUM(Amount)

Error I Get:

Msg 8120, Level 16, State 1, Line 1 Column 'dbo.PbrChargeTransactions.ServiceDate' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

I was hoping someone would be kind enough to explain to me why this is not working?

Thank you in advance for any help!

Possible duplicate of GROUP BY without aggregate function

Joe C
– Joe C

2017-01-19 21:25:00 +00:00
Commented Jan 19, 2017 at 21:25 — Joe C
– Joe C, Commented Jan 19, 2017 at 21:25

Gordon Linoff · Accepted Answer · 2017-01-19 21:22:10Z

1

I think the message is pretty clear. All unaggregated columns should be in the GROUP BY. So, remove the columns you aren't using:

SELECT HCIS, SUM(Amount) AS Total
FROM dbo.PbrChargeTransactions
WHERE HCIS = 'ASL' AND
      ServiceDate Between '2016-01-01' AND '2016-12-31' AND
      PrimeInsMnemonic = 'MED' AND
      TIN in ('460224598','46-0224598')
GROUP By HCIS
ORDER BY SUM(Amount)

answered Jan 19, 2017 at 21:22

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

SkysLastChance Over a year ago

Thank you so much. I was just making in harder on myself. I really appreciate the help.

Quan Le · Accepted Answer · 2017-01-19 21:30:06Z

0

You should include every column in Select statement to repeat in Group By if they are not in aggregate function

SELECT HCIS,ServiceDate,PrimeInsMnemonic,TIN,Amount,SUM(Amount) AS Total FROM dbo.PbrChargeTransactions WHERE HCIS = 'ASL' AND ServiceDate Between'01/01/16' and '12/31/16' AND PrimeInsMnemonic = 'MED' AND TIN in ('460224598','46-0224598') GROUP By HCIS,ServiceDate,PrimeInsMnemonic,TIN,Amount ORDER BY SUM(Amount)

answered Jan 19, 2017 at 21:30

Quan Le

113 bronze badges

Comments

mircoso · Accepted Answer · 2017-01-19 21:35:44Z

0

it is as they say, you have to enter in the group by columns in the select.

SELECT HCIS,ServiceDate,PrimeInsMnemonic,TIN,Amount,SUM(Amount) AS Total
FROM dbo.PbrChargeTransactions
WHERE HCIS = 'ASL'
AND ServiceDate Between'01/01/16' and '12/31/16'
AND PrimeInsMnemonic = 'MED'
AND TIN in ('460224598','46-0224598')
GROUP By HCIS,ServiceDate,PrimeInsMnemonic,TIN,Amount
ORDER BY SUM(Amount)

answered Jan 19, 2017 at 21:35

mircoso

113 bronze badges

Collectives™ on Stack Overflow

Groups and Aggregate functions questions

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related