SQL statement to generate a table of totals by month

Question

I am very unfamiliar with advanced SQL. Lets say I have the following table (in Access - using Jet 4.0 OLEDBAdapter in VB.NET).

Table - Items

ID     Date      Account  Amount
-----  ------    -------  ------
1      1/1/2013  Cash     10.00
2      2/1/2013  Cash     20.00
3      1/2/2013  Cash     30.00
4      2/2/2013  Cash     40.00
5      1/1/2013  Card     50.00
6      2/1/2013  Card     60.00
7      1/2/2013  Card     70.00
8      2/2/2013  Card     80.00

And I want to generate the following - totals for each account per month

Table - Totals

Account  Jan       Feb
-----    -----     ------
Cash     30.00     70.00
Card     110.00    150.00

Is this possible using one SQL statement. I can do it in two but it is very slow.

Edit - the closest I have got is this - but it doesn't generate columns

SELECT    accFrom, Sum(amount) 
FROM      Items
WHERE     Year(idate) = '2012' 
GROUP BY  Month(idate), accFrom

if you want to create a column for each month you'll want to get familiar with `pivot`. This link has some info http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx — Goose, Jan 02 '14 at 23:59

HansUp · Answer 1 · 2014-01-03T07:05:21.770

Using your sample data, this is the output I got from the query below with Access 2010.

Account 2013-01 2013-02
------- ------- -------
Card    $120.00 $140.00
Cash     $40.00  $60.00

My totals don't match your expected output. I suspect your date values were d-m-yyyy format, but my US locale interpreted them as m-d-yyyy. It's better to present dates in yyyy-m-d format to avoid that confusion.

Anyway this query formats the dates as yyyy-mm, and then pivots to generate the columns for each year-month combination. So it will accommodate a growing date range without requiring you to modify the query. And, as the date range grows, you could eventually add a WHERE clause to limit to columns to a convenient subset.

TRANSFORM Sum(i.Amount) AS SumOfAmount
SELECT i.Account
FROM Items AS i
GROUP BY i.Account
PIVOT Format(i.Date,'yyyy-mm');

Dour High Arch · Accepted Answer · 2014-01-03T01:06:48.787

2

Since there are exactly 12 months in a year, you do not need to pivot; just calculate the sum for each month:

SELECT Account,
    Sum(IIF(Month(Date)=01, Amount, 0)) AS Jan,
    Sum(IIF(Month(Date)=02, Amount, 0)) AS Feb,
    Sum(IIF(Month(Date)=03, Amount, 0)) AS Mar,
    Sum(IIF(Month(Date)=04, Amount, 0)) AS Apr,
    Sum(IIF(Month(Date)=05, Amount, 0)) AS May,
    Sum(IIF(Month(Date)=06, Amount, 0)) AS Jun,
    Sum(IIF(Month(Date)=07, Amount, 0)) AS Jul,
    Sum(IIF(Month(Date)=08, Amount, 0)) AS Aug,
    Sum(IIF(Month(Date)=09, Amount, 0)) AS Sep,
    Sum(IIF(Month(Date)=10, Amount, 0)) AS Oct,
    Sum(IIF(Month(Date)=11, Amount, 0)) AS Nov,
    Sum(IIF(Month(Date)=12, Amount, 0)) AS "Dec"
FROM Items
WHERE Year(Date) = 2013
GROUP BY Account

edited Jan 03 '14 at 01:06

answered Jan 03 '14 at 00:43

Dour High Arch

21,513
29
75
90

1

Is tagged as MSAccess – Steve Jan 03 '14 at 00:53
Thanks. I've converted to MSAccess like this `SELECT accFrom, Sum(IIf(Month(idate)=01,amount,0)) AS Jan, Sum(IIf(Month(idate)=12,amount,0)) AS 'Dec' FROM Items WHERE idate BETWEEN #2013-01-01# AND #2014-01-01# GROUP BY accFrom` but how can I make it work for more than a year - ie the table starts Jan 1996 and ends on the present day (it is an accounting programme)? Thanks Andy – Andy Powell Jan 03 '14 at 00:56
Apologies, I have fixed for MSAccess syntax. – Dour High Arch Jan 03 '14 at 01:02
Thank you. Can this be expanded for more than one year (ie a Jan - Dec column for each year)? Andy – Andy Powell Jan 03 '14 at 01:09
@Andy, yes, add `Year(Date)` to the `SELECT` and `GROUP BY` clauses and remove the `WHERE` clause. – Dour High Arch Jan 03 '14 at 01:13
@Andy, or do you mean something like `Sum(IIF(Month(Date)=01 AND Year(Date)=2013, Amount, 0)) AS "Jan 2013",`? You'd need a separate clause for each month of each year. – Dour High Arch Jan 03 '14 at 01:21
Thanks. Is it possible to get it so that the columns go Jan Feb .. Dec Jan Feb .. Dec Jan Feb .. Dec etc.. The above has columns Jan .. Dec and then the account and year as single columns with rows for each account/year combination. What I'd really like is just account as the first column and then each row starting with the very first month in the database and ending with the very last in columns. Many thanks for looking. Andy – Andy Powell Jan 03 '14 at 01:21
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/44366/discussion-between-dour-high-arch-and-andy-powell) – Dour High Arch Jan 03 '14 at 01:22
Sorry just saw your new comment - yes that's what I mean - but the database will continue to grow - so can I do it without having to modify the query each time - although I could do that in code I suppose.... that's a thought! Thanks Andy – Andy Powell Jan 03 '14 at 01:22

score 1 · Answer 3 · answered Jan 03 '14 at 00:16

1

Goose is right, you'll need to pivot on your Date column and use SUM() as the aggregate.

The syntax will look something similar to:

SELECT account, [1/1/2013] as jan, [2/1/2013] as feb, ... -- each month you want to select 

FROM
(
    SELECT date, account, amount FROM items
) 
PIVOT
(
SUM(amount) FOR date IN 
(  
    [1/1/2013], [2/1/2013],  ... -- each date you want to have its own column
)

) AS pvt

answered Jan 03 '14 at 00:16

sudoNebula

183
1
6

Thanks. Is there any way to make it dynamically generate columns - the database starts in Jan 1996, can include all days in the month and is always growing - it is an accounting programme. Thanks Andy – Andy Powell Jan 03 '14 at 00:22
If you want to read up on dynamic pivot's you might check out this example: http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query. I wrote this without MS-Access in mind fyi. Look at Dour High Arch's answer as well, he makes a good point. – sudoNebula Jan 03 '14 at 00:46
I doubt Access has 'pivot' syntax, if not you can try 'case' pivot: select Account, sum(iif(when year(date)=2013 and month(date)=1, amount, 0) as [jan-2013], ... group by Account it is ugly, but working. – Val Bakhtin Jan 03 '14 at 00:58
This works in Access - but only groups all Jan, Feb etc.. not a separate Jan, Feb for each year `TRANSFORM Sum(amount) AS total SELECT accFrom FROM Items GROUP BY accFrom PIVOT Month(idate)` – Andy Powell Jan 03 '14 at 01:08
This is getting closer - but the column order is alphabetical and not date `TRANSFORM Sum(Items.amount) AS total SELECT Items.accFrom FROM Items GROUP BY Items.accFrom PIVOT Format(idate,'mmm yyyy');` – Andy Powell Jan 03 '14 at 01:32

SQL statement to generate a table of totals by month

3 Answers3