Posted by Bob Stearns on 06/01/06 15:27

Yannick Benoit wrote:

> I need the query to order my salesmen by their productivity:
>
> Table: Salesmen
>
> id: 1 name: bobby
> id: 2 name: steeve
> id: 3 name: john
>
> Table: Visitors sent
>
> Hour: 12 Total: 11 Salesman: 3 Sales: 3
> Hour: 12 Total: 8 Salesman: 1 Sales: 2
> Hour: 12 Total: 15 Salesman: 2 Sales: 2
> Hour: 11 Total: 4 Salesman: 1 Sales: 1
> Hour: 11 Total: 5 Salesman: 2 Sales: 2
>
>
> Productivity of the salesmen: (Sales x 100 / Total hits)
>
> Salesman: 1 Total hits: 12 Sales: 3 Prod: 400.00%
> Salesman: 2 Total hits: 20 Sales: 4 Prod: 500.00%
> Salesman: 3 Total hits: 11 Sales: 3 Prod: 366.66%
>
> I need to way to print results like:
> 2. Steeve 500.00%
> 1. Bobby 400.00%
> 3. John 366.66%
>
> Thank you very much
>
>
>
Something like the following (untested) should do:

select t1.1d, t1.name, sum(t2.sales)/sum(t2.hits)*100
from salesmen t1
join 'visitors set' on t2.salesman=t1.id
group by t1.1d, t1.name
order by sum(t2.sales)/sum(t2.hits)*100

[Back to original message]