Обсуждение: [OFFTOPIC] Query Help

Someone asked me to create a query for them that would compute some averages
for them over time.

Basically I have a view that looks like this:

 sample_request_line_item_number | integer                  |
 sample_origin                   | character varying        |
 date_of_request                 | timestamp with time zone |
 username                        | character varying        |
 date_shipped                    | date                     |
 date_difference                 | double precision         |


I have a query that does what I need:

select username, avg(date_difference) from sample_request_average_days where
date_shipped between '2002-11-01' and '2002-11-30' group by username;

Basically it is getting the average number of days it took to ship something
by salesperson, by month.

The problem is that I want it to print the results for each month for which
there are entries (without hard-coding dates).

Is there any easy way to do this that I'm missing?

Any help appreciated. Please reply off-list to minimize noise.

Thanks,
Hunter

Try:

select username, avg(date_difference) from sample_request_average_days
group by salesperson, to_char( date_shipped, 'YYYYMM');


Hunter Hillegas wrote:
>
> Someone asked me to create a query for them that would compute some averages
> for them over time.
>
> Basically I have a view that looks like this:
>
>  sample_request_line_item_number | integer                  |
>  sample_origin                   | character varying        |
>  date_of_request                 | timestamp with time zone |
>  username                        | character varying        |
>  date_shipped                    | date                     |
>  date_difference                 | double precision         |
>
> I have a query that does what I need:
>
>
> date_shipped between '2002-11-01' and '2002-11-30' group by username;
>
> Basically it is getting the average number of days it took to ship something
> by salesperson, by month.
>
> The problem is that I want it to print the results for each month for which
> there are entries (without hard-coding dates).
>
> Is there any easy way to do this that I'm missing?
>
> Any help appreciated. Please reply off-list to minimize noise.
>
> Thanks,
> Hunter
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org