|
|
Posted by Bob Stearns on 06/01/06 19:25
Yannick Benoit wrote:
> 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
>
>
>
Here is a completely debugged script, using your data, with the answers
from your specification rather than you example. GO is my GUI's
statement separator. I rearranged the visitors_sent table to make the
primary key stand out.
create table salesmen(
id smallint not null,
name varchar(10),
primary key(id))
go
insert into salesmen
values (1,'bobby'),
(2,'steeve'),
(3,'john')
go
create table visitors_sent (
salesman smallint not null,
hour smallint not null,
total smallint,
sales smallint,
primary key(salesman,hour))
go
insert into visitors_sent
values (3, 12, 11, 3),
(1, 12, 8, 2),
(2, 12, 15, 2),
(1, 11, 4, 1),
(2, 11, 5, 2)
go
select t1.id, t1.name, sum(t2.sales)*100/sum(t2.total) as pct
from salesmen t1
join visitors_sent t2 on t2.salesman=t1.id
group by t1.id, t1.name
order by sum(t2.sales)*100/sum(t2.total)
go
The results were:
ID NAME PCT
----- ------- ------
2 steeve 20
1 bobby 25
3 john 27
[Back to original message]
|