CODEWITHSUNDEEP
mysqldategroup-bysum
Oli
Oli

Reputation: 11

How to sum transactional data by nominal account and month

Almost a complete novice at sql, trying to find my way around - I have a table of transactional data from our accounting software showing date (in dd-mm-yyyy format), nominal account and amount for each transaction. I am looking to have an output table which sums the amount posted to 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 very simple, but just can't get it!

Any and all help appreciated! Thanks!

Appreciate all the help so far, but I still can't get it to work. Perhaps I need to give more info as (being a novice) it may be that I'm doing something ridiculous that means it'll never work. So, I am using Microsoft Query to access a table via an ODBC link. The table is called AUDIT_JOURNAL.

I import the table and choose to view in MS Query. I then look at the View SQL option. It reads as follows:

SELECT AUDIT_JOURNAL.DATE,  
AUDIT_JOURNAL.NOMINAL_CODE,  
AUDIT_JOURNAL.AMOUNT,  
AUDIT_JOURNAL.FOREIGN_AMOUNT  
FROM AUDIT_JOURNAL AUDIT_JOURNAL  
WHERE (AUDIT_JOURNAL.DATE>={d '2015-02-01'})

It returns a table of transactions with the 4 columns listed above.

I am trying to condense these into a summarise table, showing one record for each Month, Year and NOMINAL_CODE combination.

My first issue is that I do not have a Month or Year field, just the DATE field which is in the format yyyy-mm-dd.

Having tried to use DATEPART(), YEAR() or MONTH() expressions on the DATE column I repeatedly get an error saying Column Not Found.

It happens when I do the following, for example: 

SELECT YEAR(AUDIT_JOURNAL.DATE) AS Year,  
AUDIT_JOURNAL.NOMINAL_CODE,  
AUDIT_JOURNAL.AMOUNT,  
AUDIT_JOURNAL.FOREIGN_AMOUNT  
FROM AUDIT_JOURNAL AUDIT_JOURNAL  
WHERE (AUDIT_JOURNAL.DATE>={d '2015-02-01'})

Upvotes: 1

Views: 112

Answers (2)

Ed Gibbs
Ed Gibbs

Reputation: 26363

These functions are needed for your query.

  • The MONTHNAME function returns the name of the month
  • The YEAR function returns the year

Beyond that, you need to GROUP BY to sum everything. Make sure you get comfortable with doing this; it's a crucial SQL skill.

Here's a first shot at the query:

SELECT
  MONTHNAME(`Date`) AS The_Month,
  YEAR(`Date`) AS The_Year,
  Nominal_Code,
  SUM(Amount) AS Total_Amount
FROM MyTable
GROUP BY
  MONTHNAME(`Date`),
  YEAR(`Date`),
  Nominal_Code
ORDER BY
  The_Year,
  The_Month,
  Nominal_Code

That will give results, except notice that February in the results comes before January. That's because it's ordering by the month name. You'll want to order by the month number, which is the MySQL MONTH function. To have the month number be part of the query you'll also need to GROUP BY it:

SELECT
  MONTH(`Date`) AS Month_Number,
  MONTHNAME(`Date`) AS The_Month,
  YEAR(`Date`) AS The_Year,
  Nominal_Code,
  SUM(Amount) AS Total_Amount
FROM MyTable
GROUP BY
  MONTH(`Date`),
  MONTHNAME(`Date`),
  YEAR(`Date`),
  Nominal_Code
ORDER BY
  The_Year,
  Month_Number,
  Nominal_Code

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

For the month you can try this select 

select  date_format(month(STR_TO_DATE(date, '%m/%d/%Y'), '%m %Y') as month, nominal_code, sum(Amount)
from my_table
group by month

Upvotes: 0

Related Questions