sql query help (1 Viewer)

[rkrause]

I am having trouble with my query in sql 2005.
what i have is

select Producer, weight,pickup,pickupdate, scc
from table1
where pickupdate between 4-1-10 and 4-23-10

what i want to do is average all the producers SCC numbers up and divide by the pickups. im having a heck of a time getting it to come out right.

any help would be great.

 

[Subcancel]

select Producer, weight,pickup,pickupdate, scc, (Avg(scc)/Count(pickups)) As Avg
from table1
where pickupdate between 4-1-10 and 4-23-10

Try this..

 

[rkrause]

When i executed this, my results came back with nothing?

 

[JamesMcS]

Try putting your dates in mm/dd/yyyy format, with # on either side of both

 

[rkrause]

select Producer, weight,pickup,pickupdate, scc, (Avg(scc)/Count(pickup)) As Avg
from v_pp_ProducerLoadsAndLabs
where pickupdate between '4-21-2010' and '4-23-2010' and producer = '1650'
group by Producer, weight,pickup,pickupdate, scc

thats what i have so far. It works but i attached a screenshot.
what i really want is: add up the 5 SCC values(115,120,131,117,127) and divide that by 5.

 

[FoFa]

The Avg(scc) should do that for you, however you need understand what your GroupBy is doing.
It will only AVG for each unique row your groupby returns.
Since weight, pickupdate and pickup are part of your group by, you get a unique row for each combined value. Avg is doing what you ask.

select Producer, min(pickupdate) as D1,max(pickupdate) as D2 , Min(scc) as M1, Max(scc) as M2,(Avg(scc)/Count(pickup))As MyAvg
from v_pp_ProducerLoadsAndLabs
where pickupdate between'4-21-2010'and'4-23-2010'and producer ='1650'
groupby Producer, scc