Re: How do I make this SQL more efficient?

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.