Retrieve averages for range of incremental dates...

[Jason Wells]

Hello,
Here is my table layout:
[color=blue]
> describe samples;[/color]
| user | varchar(64) | | PRI |
| host | varchar(255) | | PRI |
| command | blob | | |
| date | datetime | | PRI |
| pid | int(11) | | PRI |
| jval | smallint(6) | | |
| enabled | enum('Y','N') | | |

For each host, I want to calculate the % rows with enabled = 'Y'. I want the
% calculated for each week of available data.

I'm hoping to get something like this:

Week of | enabled %
1-01-05 | 49
1-08-05 | 50
1-15-05 | 62

Is there a way I can "group by" week?

Thanks in advance,
Jason

[Jason Wells]

Re: Retrieve averages for range of incremental dates...

Jason Wells wrote:
[color=blue]
> Hello,
> Here is my table layout:
>[color=green]
>> describe samples;[/color]
> | user | varchar(64) | | PRI |
> | host | varchar(255) | | PRI |
> | command | blob | | |
> | date | datetime | | PRI |
> | pid | int(11) | | PRI |
> | jval | smallint(6) | | |
> | enabled | enum('Y','N') | | |
>
> For each host, I want to calculate the % rows with enabled = 'Y'. I want
> the % calculated for each week of available data.
>
> I'm hoping to get something like this:
>
> Week of | enabled %
> 1-01-05 | 49
> 1-08-05 | 50
> 1-15-05 | 62
>
> Is there a way I can "group by" week?
>
> Thanks in advance,
> Jason[/color]

I think I got it:
SELECT host,
count(command) BUILDS,
(SUM(enabled = 'Y')/COUNT(command)) *100 ADOPTION,
min(date) "Week Of"
FROM samples
GROUP BY host, WEEK(date)
ORDER BY host, WEEK(date);