Summary Query 1

[Aterlatus]

Hi All,

I'm just playing about with a few queries, and I've come across a bit of a brick wall...

I have a list of engineers with scores, like so:


Bloggs 1 0 0 1 1 0 1 1 0
Smith 1 1 0 1 1 0 0 0 1
Bloggs 1 0 0 0 0 1 1 0 1
Jones 1 1 1 1 0 0 1 1 0
Bloggs 1 1 0 0 0 0 1 0 1
Smith 0 0 1 0 1 0 0 1 1

I want to create a query that will give me just one occurence of each engineer, with the average of their scores for each column, thus the query for this data would return:

Bloggs 1.0 0.3 0.0 0.3 0.3 .............
Smith 0.5 0.5 0.5 0.5 1.0 .............
Jones 1.0 1.0 1.0 1.0 0.0 .............


If y'all need a more precise description, then please say so and I'll see what I can do.

If this makes sense, then does anyone know how I can do this?

Thanks,

Steve Sherlock

 

[r937]

[tt]select engineer, avg(score1), avg(score2), ...
from yourtable
group
by engineer[/tt]

rudy

 

[Aterlatus]

OK, that part works great

Thanks.

Now for the next dilemma though.

This table also contains a date that the job was complete. I want the query to only show results for last month, without affecting the grouping (can you tell I've tried to do this myself already? ).

Any suggestions?

Thanks

 

[r937]

just add the date condition to the WHERE clause

it's a bit tricky (because of december/january), and there are several ways to do it, but the way i prefer is to perform arithmetic on the current date to come up with the first and last dates of last month, and then just check that the date column is between those bounds

[tt]select engineer, avg(score1), avg(score2), ...
from yourtable
where somedate
between dateadd( "m", -1,
dateserial(year(date()),month(date()),01) )
and dateadd( "d", -1,
dateserial(year(date()),month(date()),01) )
group
by engineer[/tt]

 

[Aterlatus]

Works grand. Thank you