Re: SQL Query Question.

"Karen Hill" <karen_hill22_at_yahoo.com> wrote in message news: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
>

First, what Jay Dee said.

Second, DECODE is just a poor man's CASE construct. If your DBMS doesn't have DECODE, maybe it does have the CASE construct. If it does, you are going to have some work to do to use CASE where your Oracle Admins used DECODE. The semantics overlap, but the syntax is different.

Third, the column "Amount" is poor data modeling. People, even experts, don't agree on what constitutes good data modeling. But I'll offer the opinion that a dollar amount of lost sales doesn't belong in the same column as a dollar amount of sales that were sold. If you had two columns Sold_Amount and Lost_Amount, this problem would be straight forward. Of course, every entry would have to have either a zero or a NULL (shudder) in the column that does not pertain. And there could be other uses of the data where my suggestion could cause even worse problems, because it isn't in 1NF.

Fourth, it seems to me that you are going to miss one set of salesmen. These are the salesmen who have no entries in the leads table, or either kind, "sold" or "lost". Is it your intention to leave these salesmen off the report? Alternatively, am I mistaken about the consequences of your code? Received on Fri Dec 23 2005 - 21:59:43 CET