# Thread: Query 5% of each other

1. Registered User
Join Date
Oct 2008
Posts
2

## Unanswered: Query 5% of each other

Hi:

Currently i am doing an application which administers sales of certain products. I am trying to make a report that shows all pairs of salesman whose sales were 5% of each other on every product, any ideas on how to do this??

the tables look something like this
Inventory:
___________________
|ID-I | Description |
-----------------------------
|1a | Pen |
|2a | Pencil |
|3a | Eraser |
-----------------------------

Sales:
___________________
|ID-S | ID-I | Quantity|
-----------------------------
|1 | 1a | 40.00 |
|1 | 2a | 24.00 |
|1 | 3a | 30.00 |
|2 | 1a | 40.00 |
|2 | 2a | 22.00 |
|2 | 3a | 30.00 |
|3 | 2a | 30.00 |
------------------------------
Employee
__________________________
|ID-S | Name |
------------------
|1 | John |
|2 | Jane |
|3 | Moe |

So the result that i am looking would look something like
(John, Jane)
(Jane, John)

Moe wouldn't be answer since he didnt sale the same products as john and jane he still needs to sale pens and erasers even though he sold the same quantity as John and Jane for erasers

thanks
lonrot

2. Registered User
Join Date
May 2003
Location
USA
Posts
5,737
What school is this for?

3. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Originally Posted by lonrot
... all pairs of salesman whose sales were 5% of each other on every product
Try a self-join with these two conditions (equality on product, and "sales1 > 1.05*sales2") as the join condition.
You'll probably also need some kind of "group by" (& having count(*) = 3) if by "every product" you really mean all 3 products.
Try building up the query logic step by step, by using CTEs.
Last edited by Peter.Vanroose; 10-26-08 at 06:34.

4. Registered User
Join Date
May 2003
Location
USA
Posts
5,737
Originally Posted by Peter.Vanroose
Try a self-join with these two conditions (equality on product, and "sales1 > 1.05*sales2") as the join condition.
You'll probably also need some kind of "group by" (& having count(*) = 3) if by "every product" you really mean all 3 products.
Try building up the query logic step by step, by using CTEs.
What makes you think he/she is using DB2?

5. Registered User
Join Date
Oct 2008
Posts
2
thanks Peter.Vanroose that gave me a good start and i can figure it out from there. I am going to do a subquery where i get only the salesman who sold the same products to get the for "every product"

6. Registered User
Join Date
Sep 2004
Location
Belgium
Posts
1,126
Originally Posted by Marcus_A
What makes you think he/she is using DB2?
Because this is the DB2 forum.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•