CODEWITHSUNDEEP
sql
SkysLastChance
SkysLastChance

Reputation: 221

Groups and Aggregate functions questions

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!

Upvotes: 0

Views: 44

Answers (3)

mircoso
mircoso

Reputation: 11

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)

Upvotes: 0

Quan Le
Quan Le

Reputation: 11

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)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270181

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)

Upvotes: 1

Related Questions