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](http://stackoverflow.com/questions/20074562/group-by-without-aggregate-function) — Joe C, Jan 19 '17 at 21:25

score 1 · Accepted Answer · answered Jan 19 '17 at 21:22

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 '17 at 21:22

Gordon Linoff

1,242,037
58
646
786

Thank you so much. I was just making in harder on myself. I really appreciate the help. – SkysLastChance Jan 19 '17 at 21:40

score 0 · Answer 2 · answered Jan 19 '17 at 21:30

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)

score 0 · Answer 3 · answered Jan 19 '17 at 21:35

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)

Groups and Aggregate functions questions

3 Answers3