Re: SQL Query Question.

Karen Hill wrote:
> 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
>

That "SELECT *" gives two columns named 'salesperson.' One might think that they're the same thing -- but they're not. The first 'salesperson' has NULLs added to it because of the RIGHT JOIN.

You might try (and I'm not sure I've got the COUNTs and SUMs the way you want them)

SELECT people.salesperson
,        sold.Totaled
,        lost."Number of Lost"
,        sold."Number of Sales"
FROM ( SELECT DISTINCT salesperson AS salesperson
        FROM leads ) PEOPLE
LEFT JOIN ( SELECT salesperson
             ,      COUNT(*) AS "Number of Sales"
             ,      SUM(amount) AS "Totaled"
             FROM leads
             GROUP BY salesperson ) sold USING (salesperson)
LEFT JOIN ( SELECT salesperson
             ,      COUNT(*) as "Number of Lost"
             FROM leads
             GROUP BY salesperson ) lost USING (salesperson)