select extract (year from CRIME_DATE) year,
SUM (CRIME_NO)
from crime
group by select extract (year from CRIME_DATE) CRIME_DATE
order by select extract (year from CRIME_DATE) year
I am trying to use the above query to produce a chart in application builder in apex but to make sure it works i am testing it on SQL commands but i keep getting the missing expression error can anyone help?
select extract (year from CRIME_DATE) year,
SUM (CRIME_NO)
from crime
group by extract (year from CRIME_DATE)
order by extract (year from CRIME_DATE)
This should fix the problems. You had additional column names after the extract in group by/order by (if you want multiple columns to identify these by you should use commas) and they also had selects in the line which should have been there.
For the chart error....maybe try this (little rusty on Oracle so not sure this will work):
select extract (year from CRIME_DATE) year,
SUM (CRIME_NO)
from crime
group by extract (year from CRIME_DATE)
order by year
order by, not in the group by.Addressing just the chart error - Marshall Tigerus has addressed your initial error - the error from the chart builder is quite clear, and there are samples. You need to use specific column aliases; and you also need to COUNT rather than SUM, I think:
select NULL link,
extract (year from CRIME_DATE) label,
COUNT (CRIME_NO) value
from crime
group by extract (year from CRIME_DATE)
order by extract (year from CRIME_DATE);
