Posted by Yannick Benoit on 06/01/06 17:35

Hmmm

I dont think we can make some divisions in a SELECT statement.
Anyway that one did not work :(

Thanx!

"Bob Stearns" <rstearns1241@charter.net> wrote in message
news:5IDfg.20$HV6.16@fe02.lga...
> 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]