0

If it's been asked before then my search function is cursed.

select postkode, gemeente, count(postkode) as total 
from leveradressen 
where land=1 
group by postkode 
order by postkode asc

postkode is a zipcode gemeente is the town name

i try to run this but i get 

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

... no idea how to make it work. I know WHY he does it. but this is an assignment to see if i can keep this job and i'm utterly failing at it.

what's happening is he thinks that there's too many place names to pick from. but every place name matches every zipcode. there are never any deviations. 2000 will always be antwerpen, for example.

and no i can't fix it by adding a 'group by' because that's in there and i can't use aggregate functions because both postkode and plaatsnaam are both nvarchar.

i'm out of ideas.

Gaius Vulcanus Duodecimus
  • 5
  • 6
  • It's been asked. Up your search-fu by learning to search the error: [Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.](https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e) – random_user_name Jun 04 '18 at 13:26
  • 1
    Possible duplicate of [Reason for Column is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause](https://stackoverflow.com/questions/13999817/reason-for-column-is-invalid-in-the-select-list-because-it-is-not-contained-in-e) – Sebastian Brosch Jun 04 '18 at 13:27
  • Question: you said 2000 is always Antwerp but Antwerp is always 2000? In other words, it's a 1-to-1 relationship? – Robert Kock Jun 04 '18 at 13:44
  • @RobertKock yes. they're postal codes. x is y and y is x. one's just a number thingy and the other's a name thingy. but they're same. – Gaius Vulcanus Duodecimus Jun 04 '18 at 14:18

2 Answers2

1

You should group by gemeente as well.
So:

select postkode, gemeente, count(postkode) as total 
from leveradressen 
where land=1 
group by postkode, gemeente
order by postkode asc

Not sure what you mean with i can't fix it by adding a 'group by' because that's in there

Robert Kock
  • 5,795
  • 1
  • 12
  • 20
0

You could use:

select postkode, MIN(gemeente) AS gemeente, count(postkode) as total 
                 -- here goes agg function
from leveradressen 
where land=1 
group by postkode 
order by postkode asc
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275