Challenging SQL Query Problem. Can you solve it?

From: Karen Hill <karen_hill22_at_yahoo.com>
Date: 22 Dec 2005 12:59:21 -0800
Message-ID: <1135285161.440479.242870@g14g2000cwa.googlegroups.com>

X-No-Archive:yes

Hi. I have heard that Oracle Admins are considered the best in the world. I thought I'd see if you guys can help me with an SQL query that has had me pull out some hairs off the top off my head.

I have a Query Called "Lost Sales" which looks like this:

SELECT leads.salesman AS Salesperson, COUNT(lost) AS [Number of Lost] FROM leads INNER JOIN salesman ON leads.salesman = salesman.salesman WHERE lost<>0
GROUP BY leads.salesman;

I then have a query called "Sold Sales" which looks like this:

SELECT leads.salesman AS Salesperson, SUM(amount) AS Totaled, COUNT(sold) AS [Number of Sales] FROM leads INNER JOIN salesman ON leads.salesman = salesman.salesman
WHERE sold <> 0
GROUP BY leads.salesman;

I then have a query called "Total" which combines the two previous queries and looks like this:

SELECT * FROM [Lost Sales] RIGHT JOIN [Sold Sales] ON [Lost Sales].[Salesperson] = [ Sold Sales.Salesperson];

Now my problems. The leads table looks like this salesperson
unique_id (primary key)
amount
sold (yes/no)
lost (yes/no)
pending (yes/no)

• yes = -1 and no = 0

When I run the "Total" query I get two salesperson columns in the result where I'd prefer to get only one. And sometimes when the person doesn't have a sold sale or lost sale checked in the leads table their name is not there. Here is an example to help you visualize it:

Lost Sales.Salesperson|Number of Lost|Sold Sales.Salesperson|Totaled|Number of Sales

---

|Jim | 3 | Jim | $1,000 | 3 |

---

| | | Greg | $2,000 | 4 |

What I would like the result to be is this though:

Salesperson | Totaled | Number of Lost | Number of Sales

---

Jim             |  $1,000  |  3                      |    3


---------------------------------------------------------------------------------------

Greg           |  $2,000  |  0                      |    4

Received on Thu Dec 22 2005 - 14:59:21 CST