group by years

[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]

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]

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...