SQL/HSQLDB query and sub-query in Aggregate Function -

my database looks (very simple) , called "ridedate": bikedate bike miles looking achieve query each month total(sum) across years, average(avg) across years, , total specific year (where year("date")= '2014"). (i don"t have exact code in front of me due power fluctuations, pushing me onto ipad (high winds , wet/heavy snow)).

my attempt goes this:

select month("bikedate") "month", sum("miles") "smiles", avg("amiles") "average", (select month("bikedate") sum("miles") year("bikedate") = '2014') "2014" "ridedate" group month("bikedate") order month("bikedate") asc 

the results should be: (month) (sum of month on years) (avg of month on years) (sum of month '14)

the last column not collate 'group month' , gives sum whole year.

how can write sub-query sum across iterated month of main query selected year? there way of solving this?

you can try cross join

select * ( (select month("bikedate") "month", sum("miles") "smiles", avg("amiles") "average", "ridedate" group month("bikedate"))a cross join  (select sum("miles") "yearsum" "ridedate" year("bikedate") = '2014')b ) results