0

I would like to make a statistics of how many members are of a given age in the population, and the total number of their cars.

So far I've got this:

select round((SYSDATE - sz.born)/365,0), count(carid) 
from person sz inner join cars k
  on sz.personid = k.ownerid
  group by 1

Says it's not a group by expression and I don't know why.

DJB
  • 31
  • 1
  • 4
  • 5
    because it's not?...you should use `GROUP BY round((SYSDATE - sz.born)/365,0)` – Lamak Jun 15 '16 at 15:32
  • I thought 1 in last row meant the first part of the select – DJB Jun 15 '16 at 15:38
  • 1
    the group by is processed before the columns listed in the select clause are processed. If you take that into consideration, you should be able to see why "group by 1" can't possibly know that you're trying to refer to the first column in the select list. – Boneist Jun 15 '16 at 15:41
  • 1
    You can reference columns by number in the `ORDER BY` clause but not `GROUP BY` or `WHERE`. I think it's better practice to always use the explicit column names, makes it easier to read and won't break if the select list gets altered. – Hart CO Jun 15 '16 at 15:50
  • Some systems support column numbers on grouping and/or ordering clauses, but it's not standard and is not a best practice. – D Stanley Jun 15 '16 at 17:07
  • @HartCO Not all systems support it in the order by. – D Stanley Jun 15 '16 at 17:07
  • @DStanley Do you know of any that don't support `ORDER BY` ordinal? I've not encountered one that doesn't yet, but my experience is limited. – Hart CO Jun 15 '16 at 17:19

0 Answers0