Home > Archive > PHP SQL > June 2006 > query needed please help
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
query needed please help
|
|
| Yannick Benoit 2006-06-01, 7:00 pm |
| 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
| |
| Bob Stearns 2006-06-01, 7:00 pm |
| 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
| |
| strawberry 2006-06-01, 7:00 pm |
| I don't understand how the numbers add up - unless you mean
(Total/Sales)*100
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
| |
| Yannick Benoit 2006-06-01, 7:00 pm |
| 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:
>
> 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
| |
| Bob Stearns 2006-06-01, 7:00 pm |
| 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...
>
>
>
>
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
|
|
|
|
|