Take this query I'm running from the mysql commandline:

SELECT name,
SUM(joined_0) AS J0,
SUM(joined_1) AS J1,
ABS(SUM(joined_0) - SUM(joined_1)) AS J_Diff,
SUM(joined_0)/(SUM(joined_0) + SUM(joined_1))*100 AS J0_Pcnt,
SUM(joined_1)/(SUM(joined_0) + SUM(joined_1))*100 AS J1_Pcnt
FROM ps_people p,
ps_pdata d,
ps_pdata_etc e
WHERE d.dataid = e.dataid AND
d.pid = p.pid
GROUP BY d.plrid
HAVING J0 > 0 AND
J1 > 0
ORDER BY J_Diff ASC
LIMIT 20;




1) Why is it I can't use J0 and J1 in the definition for J_Diff and
J0_Pcnt, J1_Pcnt

2) Is there any way to do it that I'm missing here?

I guess mainly I want ot save typing and it seems really redundant and
error prone to write a query the way I wrote it, and aliasing seemed
like it would make things less cluttered but all I get is 'Unknown
Column'

Thanks for any help.

You can't reference aliases in the field list or Where clauses.

You can in the Having and Order By though.

Tigger


Stan R. wrote:
> Take this query I'm running from the mysql commandline:
>
> SELECT name,
> SUM(joined_0) AS J0,
> SUM(joined_1) AS J1,
> ABS(SUM(joined_0) - SUM(joined_1)) AS J_Diff,
> SUM(joined_0)/(SUM(joined_0) + SUM(joined_1))*100 AS J0_Pcnt,
> SUM(joined_1)/(SUM(joined_0) + SUM(joined_1))*100 AS J1_Pcnt
> FROM ps_people p,
> ps_pdata d,
> ps_pdata_etc e
> WHERE d.dataid = e.dataid AND
> d.pid = p.pid
> GROUP BY d.plrid
> HAVING J0 > 0 AND
> J1 > 0
> ORDER BY J_Diff ASC
> LIMIT 20;
>
>
>
>
> 1) Why is it I can't use J0 and J1 in the definition for J_Diff and
> J0_Pcnt, J1_Pcnt
>
> 2) Is there any way to do it that I'm missing here?
>
> I guess mainly I want ot save typing and it seems really redundant and
> error prone to write a query the way I wrote it, and aliasing seemed
> like it would make things less cluttered but all I get is 'Unknown
> Column'
>
> Thanks for any help.

Tigger wrote:
>
> Stan R. wrote:
>> Take this query I'm running from the mysql commandline:
>>
[...]example
>> 1) Why is it I can't use J0 and J1 in the definition for J_Diff and
>> J0_Pcnt, J1_Pcnt
>>
>> 2) Is there any way to do it that I'm missing here?
>>
>> I guess mainly I want ot save typing and it seems really redundant
>> and error prone to write a query the way I wrote it, and aliasing
>> seemed like it would make things less cluttered but all I get is
>> 'Unknown Column'
>>
>> Thanks for any help.
>
> You can't reference aliases in the field list or Where clauses.
>
> You can in the Having and Order By though.
>
> Tigger

Thank you. I could swear other DB engines allowed this. I remember doing
that sort of thing in Oracle a couple years ago. It seems to make so
much sense to me, to eliminate redundancy. Actualyl if MYsql allowed
subqueries the way Oracle did (like in the FROM list) this wouldn't be
an issue at all

--
Stan
Merry Christmas

Stan R. wrote:
> Actualyl if MYsql allowed
> subqueries the way Oracle did (like in the FROM list)

It does, although I usually use JOINs instead

paul_lautman@yahoo.com says...
>
> Stan R. wrote:
> > Actualyl if MYsql allowed
> > subqueries the way Oracle did (like in the FROM list)
>
> It does, although I usually use JOINs instead

Oracle's
WITH <subquery> SELECT
and
FROM <subquery>
allow you to do some things that simple ANSI joins just can't match.

Geoff M

Geoff Muldoon wrote:

> paul_lautman@yahoo.com says...
> >
> > Stan R. wrote:
> > > Actualyl if MYsql allowed
> > > subqueries the way Oracle did (like in the FROM list)
> >
> > It does, although I usually use JOINs instead
>
> Oracle's
> WITH <subquery> SELECT
> and
> FROM <subquery>
> allow you to do some things that simple ANSI joins just can't match.
>
> Geoff M

Yes it does, but this question is about doing things in MySQL!