CODEWITHSUNDEEP
sqloracle-database
user3499533
user3499533

Reputation: 23

Oracle SQL query for Year

select null link, CRIME_DATE label, SUM(CRIME_NO) value1
from  "C3348242"."CRIME"
group by CRIME_DATE 
order by CRIME_DATE DESC

I currently have this SQL query but it is not giving me the results that I desire. I want to select all the crimes that occurred in a certain year and order them by the different years for example all the crime 2013, 2014 etc. I want to get the year from the Crime_date column which is stored as DATE datatype. Can anyone help?

Thanks

Upvotes: 1

Views: 148

Answers (1)

Patrick Hofman
Patrick Hofman

Reputation: 156928

You can get the year using extract:

select extract(year from CRIME_DATE) year
,      null link
,      CRIME_DATE label
,      SUM(CRIME_NO) value1
from   "C3348242"."CRIME"
group
by     extract(year from CRIME_DATE)
,      CRIME_DATE 
order
by     extract(year from CRIME_DATE) desc
,      CRIME_DATE DESC

You could also use to_char(crime_date, 'yyyy')

Upvotes: 1

Related Questions