group by years

sorin_zan

hi everybody, I have a table whith a lot an invoice (2007, 2008, 2009 etc)and I want to group the invoice by years (2007, 2008 etc). A client may have many invoice every year. I try something but I didn't find a solution to SUM value of invoice for all years separately:

SELECT SUM(total_sales) AS year_1, Client
FROM vanzari_totale
GROUP BY DATEPART('yyyy', invoice_date), Client

Database is MS Access. Please is urgent. Thanks in advance!

Dave Kreskowiak

sorin_zan wrote:

Please is urgent.

It's only urgent to you. To a bunch of people who volunteer their time for free, it really doesn't matter. You have to specify one or more column names in the GROUP BY clause, not actual data.

A guide to posting questions on CodeProject[^]
Dave Kreskowiak

Dalek Dave

Not clear. Why not just pull all invoices by year? or do you need them totaled? Or do you want them sorted? More info please.

------------------------------------ I will never again mention that I was the poster of the One Millionth Lounge Post, nor that it was complete drivel. Dalek Dave CCC Link[^] Trolls[^]

Shahan Ayyub

Hi! Could you please check this one: ( without calling DatePart() )

SELECT SUM(total_sales) AS year_1, Client
FROM vanzari_totale
GROUP by invoice_date, client

Luc Pattyn

Nope. This will group on a daily basis, each distinct invoice_date value belongs to a different group. However, I have a solution, see my other message. :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

Luc Pattyn

Hi, 1. you're in the wrong forum, this is a database question, not a VB one. So the SQL or Database forums are more appropriate. 2. this works fine on SQL Server:

SELECT COUNT(1), year(payment_date) as year FROM payments GROUP BY year(payment_date)

However it fails on Access, and so does this:

SELECT COUNT(1), datepart("yyyy", payment_date) as year FROM payments GROUP BY datepart("yyyy", payment_date)

However this does it:

SELECT COUNT(1), datepart("yyyy", payment_date) as yr FROM payments GROUP BY datepart("yyyy", payment_date)

so the only problem was SQL Server did and Access didn't accept "year" as an alias (it also is a function name). [ADDED]: one can use square brackets when there is a risk of clashing with reserved keywords, like so:

SELECT COUNT(1) as [count], datepart("yyyy", payment_date) as [year] FROM payments GROUP BY datepart("yyyy", payment_date)

[/ADDED] :)

Luc Pattyn [My Articles] Nil Volentibus Arduum

modified on Wednesday, August 24, 2011 12:09 AM

sorin_zan · modified on Wednesday, August 24, 2011 12:09 AM

Thanks a lot for your kind answer, Luc! Now I'll try your suggestions.

SELECT COUNT(1) as [count], datepart("yyyy", payment_date) as [year] FROM payments GROUP BY datepart("yyyy", payment_date)

work good. But, there is a problem: 1. I would like to do something this client | 2007 | 2008 | 2009 | 2010 | 2011 | ----------------------------------------------------------- client_1 |1,200.78$ |267.89$ |.... |.... |..... | ----------------------------------------------------------- client_2 |678.67$ |5,76.00$|.... |.... |..... | means that, on each column to has a sum of all invoice from that year Sorry for my english... Could you help me again?

sorin_zan · modified on Wednesday, August 24, 2011 12:09 AM

LUC, I think that the solution is:

TRANSFORM Sum(Vanzari.Valoare) AS SumOfValoare
SELECT Vanzari.Client
FROM Vanzari
GROUP BY Vanzari.Client
PIVOT Month([Data]);

and work good, this I have a column for each year and sum of invoice/year/client

sorin_zan

thanks but don't work... I find a solution:

TRANSFORM Sum(Vanzari.Valoare) AS SumOfValoare
SELECT Vanzari.Client
FROM Vanzari
GROUP BY Vanzari.Client
PIVOT Month([Data]

)

sorin_zan

OFFFF!!! It didn't work. TRANSFORM is not recognize in design mod, using Query Wizard of TableAdapter.

Shahan Ayyub

Have a look at this query:

select SUM([total_sales]) as [Total], [Client],year([invoice_date]) as [InvoDate]
FROM vanzari_totale
group by [client], year([invoice_date])

sorin_zan

thanks for your kind post, but your solution isn't one good for me because the years appear in row not in column

3545,3 2007 client_1 1
433,33 2008 client_1 1
77756,1 2009 client_1 1
3423,7 2010 client_1 1
1234,44 2011 client_1 1

I think that the only solution is to use TRANSFORM.....PIVOT Right? Or there is another solution? Please let me know if is true. Thanks a lot for your support!

Shahan Ayyub

The format you have provided for output, it seems that you want to use it in chart control? Is it right?

sorin_zan

No, it's only interogate for a datagrid. I want to do this for a rapid information of sales agents about history of customer. It's a small office application...