SQL statement help

jackace

Golden Member
Oct 6, 2004
1,307
0
0
This is for a class I'm taking but I'm stumped at how to do this specific question.

The question is:

In which years did the total donated exceed the goal for the year.

The tables are:

YEAR
year(pk), yeargoal

GIFT
amount, year(pk), donorno(pk)

DONOR
donorno(pk), dlname, dfname, dphone, dstate, dcity

I know I need to sum the amount and group it by year to get a yearly total. My problem is how do I compare this number to the yeargoal number. I can hard code a number in and get a statement that works with that number, but I can't get a statement that will compare each years sum to the goal for each year. Everything I have tried just gives me an error.

My hard coded statement that works is:

select year.year
from year, gift
where year.year = gift.year
group by year.year
having sum(amount) > 5000;

What I need to be able to do is replace 5000 with yeargoal, but that gives me an error and does not work. Any help you guys can offer is appreciated.
 

her209

No Lifer
Oct 11, 2000
56,336
11
0
SELECT HomeworkHelp FROM ATForums WHERE ForumName = 'Programming';
 

Jadow

Diamond Member
Feb 12, 2003
5,962
2
0
use a case statement in the select:

Succeeded = case when sum (amount) > 5000 then 'Y' else 'N' end
 

jackace

Golden Member
Oct 6, 2004
1,307
0
0

I tried multiple ways I know to use a subquery, but it comes down to comparing sum(amount) to yeargoal and nothing I know makes that work. I always get a syntax error. (even using a $)
 

Ktulu

Diamond Member
Dec 16, 2000
4,354
0
0
I tried multiple ways I know to use a subquery, but it comes down to comparing sum(amount) to yeargoal and nothing I know makes that work. I always get a syntax error. (even using a $)

What sql language are you using?

Maybe something like this could work:

select year.year
from year, gift
where year.year = gift.year
group by year.year
having sum(amount) > (select year.yeargoal
from year
where year.year = gift.year);
 

ravana

Platinum Member
Jul 18, 2002
2,149
1
76
maybe something like this?
Code:
select y.year, y.yeartot
from year, (select year, sum(amount) as yeartot from gift) as t
where y.year=t.year
and t.yeartot > y.yeargoal

P.S: Whoa...we're in programming now!
 

jackace

Golden Member
Oct 6, 2004
1,307
0
0
My teacher sent the database out in Access. I'm just using the SQL query writing in that. All the suggestions posted have given similar syntax errors to the ones I have received or errors saying a specified expression is not included in the aggregate function.
 

jackace

Golden Member
Oct 6, 2004
1,307
0
0
To give credit where it's due I got a PM from krmarks and his suggestion works.

Looking at what he posted the part I was messing up was I had to group by year AND yeargoal. After I did that my original query replacing the 5000 with yeargoal works as well.

Thank you for all the help

edit - here is the solution he gave that works

Select
a.[Year]
From
[YEAR] a
left join Gift b
on a.[year] = b.[year]
Group by
a.[Year]
,yeargoal
having
Sum(amount) > yeargoal