Re: How do I make this SQL more efficient?

Nicely done ... but you were way to generous. <g>

Daniel Morgan

Galen Boyer wrote:

> On 18 Apr 2002, galendboyer_at_yahoo.com wrote:
>
> > First, show that I'm trying to bring back either negative or positive
> > 1.
> >
> > SQL> select KEYCOLUMN,sum(decode(gender,'F',1,-1)),
> > sum(decode(gender,'M',1,-1)) from t1 group by KEYCOLUMN;
>
> I walked away and realized I wasn't checking a full case. I need one
> keycolumn to have count(keycolumn 'M') - count(keycolumn 'F') > 1. I
> added a few more rows than needed, but notice now that for keycolumn=1,
> count of 'F' is 2 while 'M' is 6, which gives us more than 1 difference
> between their counts.
>
> SQL> select * from t1 order by KEYCOLUMN,gender;
>
> KEYCOLUMN G
> ---------- -
> 1 F
> 1 F
> 1 M
> 1 M
> 1 M
> 1 M
> 1 M
> 1 M
> 2 F
> 2 F
> 2 M
> 3 M
> 4 F
> 5 F
> 5 M
>
> So, now the previous query:
>
> > SQL> select KEYCOLUMN,sum(decode(gender,'F',1,-1)),
> > sum(decode(gender,'M',1,-1)) from t1 group by KEYCOLUMN;
> >
> > KEYCOLUMN SUM(DECODE(GENDER,'F',1,-1)) SUM(DECODE(GENDER,'M',1,-1))
> > ---------- ---------------------------- ----------------------------
> > 1 -1 1
> > 2 1 -1
> > 3 -1 1
> > 4 1 -1
> > 5 0 0
>
> returns:
>
> SQL> select KEYCOLUMN,sum(decode(gender,'F',1,-1)), sum(decode(gender,'M',1,-1))
> from t1
> group by KEYCOLUMN;
>
> KEYCOLUMN SUM(DECODE(GENDER,'F',1,-1)) SUM(DECODE(GENDER,'M',1,-1))
> ---------- ---------------------------- ----------------------------
> 1 -4 4
> 2 1 -1
> 3 -1 1
> 4 1 -1
> 5 0 0
>
> and my final query, which used to return:
>
> > SQL> select KEYCOLUMN,decode(sum(decode(gender,'F',1,-1)),1,'F','') ||
> > decode(sum(decode(gender,'M',1,-1)),1,'M','')
> > from t1
> > group by KEYCOLUMN;
> >
> > KEYCOLUMN DE
> > ---------- --
> > 1 M
> > 2 F
> > 3 M
> > 4 F
> > 5
>
> Now returns:
> SQL> select KEYCOLUMN,decode(sum(decode(gender,'F',1,-1)),1,'F','') ||
> decode(sum(decode(gender,'M',1,-1)),1,'M','')
> from t1
> group by KEYCOLUMN;
>
> KEYCOLUMN DE
> ---------- --
> 1
> 2 F
> 3 M
> 4 F
> 5
>
> So, this won't do. We need to add one more function to get it back to
> one's and zeros. Here goes:
>
> SQL> select KEYCOLUMN,decode(sign(sum(decode(gender,'F',1,-1))),1,'F','') ||
> decode(sign(sum(decode(gender,'M',1,-1))),1,'M','')
> from t1
> group by KEYCOLUMN;
>
> KEYCOLUMN DE
> ---------- --
> 1 M
> 2 F
> 3 M
> 4 F
> 5
>
> Does this do what you need? Can you now put that in a subquery and
> update your table?
>
> --
> Galen deForest Boyer
> Sweet dreams and flying machines in pieces on the ground.
Received on Fri Apr 19 2002 - 10:28:54 CDT