mysql - How to sum transactional data by nominal account and month -

almost complete novice @ sql, trying find way around - have table of transactional data our accounting software showing date (in dd-mm-yyyy format), nominal account , amount each transaction. looking have output table sums amount posted each nominal account within each month & year.

my current table of data:

date--nominal_code--amount 01/01/2015--1000-£10 03/01/2015--1000-£20 15/01/2015--2000-£50 20/01/2015--2000-£10 01/02/2015--1000-£20 15/02/2015--1000-£30 20/02/2015--1000--£15 

my desired output:

month--year--nominal_code--amount jan--2015--1000--£30 jan--2015--2000--£60 feb--2015--1000--£65 

sure it's simple, can't it!

any , appreciated! thanks!

appreciate far, still can't work. perhaps need give more info (being novice) may i'm doing ridiculous means it'll never work. so, using microsoft query access table via odbc link. table called audit_journal.

i import table , choose view in ms query. @ view sql option. reads follows:

select audit_journal.date,   audit_journal.nominal_code,   audit_journal.amount,   audit_journal.foreign_amount   audit_journal audit_journal   (audit_journal.date>={d '2015-02-01'})   

it returns table of transactions 4 columns listed above.

i trying condense these summarise table, showing 1 record each month, year , nominal_code combination.

my first issue not have month or year field, date field in format yyyy-mm-dd.

having tried use datepart(), year() or month() expressions on date column repeatedly error saying column not found.

it happens when following, example:

select year(audit_journal.date) year,   audit_journal.nominal_code,   audit_journal.amount,   audit_journal.foreign_amount   audit_journal audit_journal   (audit_journal.date>={d '2015-02-01'}) 

these functions needed query.

• the monthname function returns name of month
• the year function returns year

beyond that, need group by sum everything. make sure comfortable doing this; it's crucial sql skill.

here's first shot @ query:

select   monthname(`date`) the_month,   year(`date`) the_year,   nominal_code,   sum(amount) total_amount mytable group   monthname(`date`),   year(`date`),   nominal_code order   the_year,   the_month,   nominal_code 

that give results, except notice february in results comes before january. that's because it's ordering month name. you'll want order month number, mysql month function. have month number part of query you'll need group by it:

select   month(`date`) month_number,   monthname(`date`) the_month,   year(`date`) the_year,   nominal_code,   sum(amount) total_amount mytable group   month(`date`),   monthname(`date`),   year(`date`),   nominal_code order   the_year,   month_number,   nominal_code