SQL Query Question.

From: Karen Hill <karen_hill22_at_yahoo.com>
Date: 22 Dec 2005 17:01:07 -0800
Message-ID: <1135296896.300388.316970_at_z14g2000cwz.googlegroups.com>

X-No-Archive:yes

I thought I'd see if you guys can help me with an SQL query that even Oracle admins couldn't solve without using an Oracle only function DECODE. I don't have DECODE on the DBMS I'm using, so I thought maybe the PhD SQL theorists may be able to help me. Thanks in advance!

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 Fri Dec 23 2005 - 02:01:07 CET