differen aggregations IN ONE QUERY 1

[russland]

Hi,

Hi all

I have a gui interface that allows 1 single query statement to output results. Now, I would need to output:
a) the total for each project
b) the total for each project in each year
in one query.

hmm... can I join recursivley (join itself) to solve the problem?

What if I need the total costs of Project 4 and the yearly costs next to the total? Hardcoding the years would be fine for me.

TABLE
ProjectID, Year, TotalCosts
2, 2004, 500
2, 2005, 300
3, 2004, 100
4, 2004, 200
4, 2005, 300
4, 2006, 800

RESULT I NEED
ProjectID, ProjectTotal, Year2004, Year2005, Year2006
4, 1300, 200, 300, 800

thanks for any hint

 

[LKBrwnDBA]

Use CASE statement:

Select ProjectID
           , SUM(TotalCosts) ProjectTotal
           , SUM(CASE WHEN Year=2004 Then TotalCosts ELSE 0 END) Year2004
           , SUM(CASE WHEN Year=2005 Then TotalCosts ELSE 0 END) Year2005
           , SUM(CASE WHEN Year=2006 Then TotalCosts ELSE 0 END) Year2006
  From MyTable
 Group By ProjectID;

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[russland]

I had to use IIF(condition, output1, output2) instead of CASE (using MS Access 2000). However, thanks for leading me to the solution. That's worth a start, no doubt.

 

[PHV]

using MS Access 2000
Why not simply follow the Crosstab query wizard ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[russland]

I'm not very keen on using wizards...