Re: SQL Query Question.

On 23 Dec 2005 16:34:41 -0800, Karen Hill wrote:

>X-No-Archive:yes
>Hugo Kornelis wrote:
>\
>>
>> 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
>> GROUP BY salesman
>>
>> I removed the inner join to the salesman table, since you don;t appear
>> to use any of the columns in that table.
>>
>
>Hi Hugo,
>
>Thanks for the response!
>
>The salesman table is there so a salesman shows up if they didn't have
>any activity what so ever on the leads query. I will take the lessons
>you have given me and rework it.
>
>Thanks for your help.

Hi Karen,

In that case....

SELECT          s.salesman AS Salesperson,
                SUM(CASE WHEN l.sold <> 0
                         THEN l.amount ELSE 0 END) AS Totaled,
                COUNT(CASE WHEN l.lost <>  0
                           THEN 'Countme' END) AS "Number of Lost",
                COUNT(CASE WHEN l.sold <> 0
                           THEN 'Countme' END) AS "Number of Sales"
FROM            salesman AS s
LEFT OUTER JOIN leads AS l
           ON   l.salesman = s.salesman
GROUP BY        s.salesman

Best, Hugo

-- 

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