Evaluating an aggregate 1

[Dashley]

The code below works but I'm trying to write it so that only rows when sum(s.quantity) < c.reqtotal are returned

Select s.curitemid, s.userid, sum(s.quantity) as total, c.ReqTotal , c.description
from studentdata s
Inner Join curriculum c On s.curitemid= c.curitemid
Where userid in(
select userid from users 
Where active= 1 and usertype='S'
and class in (select classid from class where enddate > GetDate() 
and startdate <= getdate() 
and upper(year) NOT Like '%STAFF%' ))
and c.reqtotal > 0
group by s.curitemid, s.userid,c.ReqTotal, c.description

I keep getting :
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

I can't figure out how to do it

Thanks


Dan

 

[SQLDenis]

what about

Select s.curitemid, s.userid, sum(s.quantity) as total, c.ReqTotal , c.description
from studentdata s
Inner Join curriculum c On s.curitemid= c.curitemid
Where userid in(
select userid from users 
Where active= 1 and usertype='S'
and class in (select classid from class where enddate > GetDate() 
and startdate <= getdate() 
and upper(year) NOT Like '%STAFF%' ))
and c.reqtotal > 0
group by s.curitemid, s.userid,c.ReqTotal, c.description
having sum(s.quantity) < c.reqtotal

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[Dashley]

Ahhhhhhhh I put having sum(s.quantity) < c.reqtotal
before the group by statment when I tried that.
Do I feel dumb today


Thanks SqlDenis

 

[SQLDenis]

When I started out I used this trick
G comes before H so it's
Group by.....
Having ....

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jtammyg]

I need to do a sumproduct in SQL Server
ALL_HOSPITALS table
days percentage days avoidable
1 0.244
2 0.127
30 0.890

The calculation would be
0.244*1+0.127*2+0.890*30

The total of the above has to be divided by the sum of days.

I am planning on having a temp table with the hosp name field and the multiplication field only as follows:


INSERT INTO temp_mul_days(hosp_name_key, mul_days)
select ah.hosp_name_key,(ah.per_total_days_avoidable*ah.days)
from all_hospitals ah
left join rft_hosp_name rhn on rhn.hosp_name_key = ah.hosp_name_key
LEFT JOIN RFT_MED_GROUP MG ON MG.MED_GROUP_KEY=AH.MED_GROUP_KEY
where mg.med_group_type = 1
group by ah.hosp_name_key

I am getting the error

Server: Msg 8120, Level 16, State 1, Line 1
Column 'ah.PER_TOTAL_DAYS_AVOIDABLE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'ah.DAYS' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How can I avoid this?

After this query I was planning on having a second temp table with the sum of the results of the above and then another temp table with the sum of the denominator.

The fourth temp table would be the division between the second and third table.

Anybody has a better idea on how to do this?

Thank you!

Tammy

 

[jbenson001]

Tammy: Start a new thread

 

[Dashley]

Tammy add these to your group by
ah.PER_TOTAL_DAYS_AVOIDABLE,ah.days



INSERT INTO temp_mul_days(hosp_name_key, mul_days)
select ah.hosp_name_key,(ah.per_total_days_avoidable*ah.days)
from all_hospitals ah
left join rft_hosp_name rhn on rhn.hosp_name_key = ah.hosp_name_key
LEFT JOIN RFT_MED_GROUP MG ON MG.MED_GROUP_KEY=AH.MED_GROUP_KEY
where mg.med_group_type = 1
group by ah.hosp_name_key,ah.PER_TOTAL_DAYS_AVOIDABLE,ah.days

 

[Dashley]

G comes before H


Got it HA Thanks