SQL statement help

[jackace]

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.

 

[Ktulu]

subquery

 

[her209]

SELECT HomeworkHelp FROM ATForums WHERE ForumName = 'Programming';

 

[Jadow]

use a case statement in the select:

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

 

[dfuze]

SELECT HomeworkHelp FROM ATForums WHERE ForumName = 'Programming';

I like that.

But are you just typing "yeargoal" or "$yeargoal" to show it is a variable?

 

[jackace]

subquery

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]

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);

 

[manlymatt83]

What DBMS man? MySQL, PostgreSQL, Oracle, MsSQL, MongoDB 😛 ?

 

[ravana]

maybe something like this?

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]

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]

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