Re: SQL Query Question.

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

Consider also the following (untested) query:

select

    salesman as salesperson,

    sum (-lost) as lost_count,
    sum (-lost*amount) as lost_amount,
    sum (-sold) as sold_count,
    sum (-sold*amount) as sold_amount
from
     leads
group by
     salesperson

This takes advantage of the fact that SOLD and LOST have a minus one value whenever they are not zero, and you can use them as counters. It's clever tricky arithmetic, and not as good as Hugo's response, but I offer it just in case your DBMS doesn't offer the CASE construct.

 And of course, you would have to to the same outer join with the salesman table in order to plug in the salespersons who had no activity in the leads table. Received on Sat Dec 24 2005 - 12:39:17 CET