Re: SQL Query Question.

On 22 Dec 2005 17:01:07 -0800, 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!
(snip)

Hi Karen,

This should work. As far as I know, it uses only ANSI-standard SQL constructs, so it should run on any ANSI-compliant database.

SELECT salesman AS Salesperson,

         SUM(CASE WHEN sold <> 0 THEN amount ELSE 0 END) AS Totaled,
         COUNT(CASE WHEN lost <>  0 THEN 'Countme' END) AS "Number of
Lost",
         COUNT(CASE WHEN sold <> 0 THEN 'Countme' END) AS "Number of
Sales"
FROM     leads

I removed the inner join to the salesman table, since you don;t appear to use any of the columns in that table.

Best, Hugo

-- 

(Remove _NO_ and _SPAM_ to get my e-mail address)