Need Sum breakout...'transform'-ish SQL

[Shrum]

I have a client that wants to generate a report (no biggie there).

The db contains various fields (in multiple tables) but ultimately the ones that I'm concerned with are:

user id, year, donation

The client wants to be able to group by sum the donations by year by user. Here's an example:

User ID|year|donation
1|1999|1.00
1|1999|2.00
2|1999|1.00
2|2000|2.00

I need to be able to generate a report like:

+1999
+Users that gave $1.00
User 2
+Users that gave $3.00
User 1

+2000
+Users that gave $2.00
User 2

The '+' grouping is on SUM by year and user. Note that user 1 does not list under $1.00 (even though he donated $1.00 but totaled $3.00 in that year)

How can I structure a SQL query so I can generate a report on it?

TIA

Sean Shrum
sean@shrum.net
sean_shrum@hotmail.com

 

[tlbroadbent]

Use SQL aggregate functions and Group By. You should be able to use a reporting tool to create a report from the result set of the query.

Select
[Year], [User ID], TotDonation=Sum(Donation)
From Table
Group By [Year], [User ID]; Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Shrum]

My thanks....not exactly what I needed but got me on the right track. Working beautifully now!

Sean Shrum

http://www.shrum.net