Professional Web Applications Themes

Question about aliasing - MySQL

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 ...

  1. #1

    Default Question about aliasing

    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.


    Stan Guest

  2. #2

    Default Re: Question about aliasing

    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: 

    Tigger Guest

  3. #3

    Default Re: Question about aliasing

    Tigger wrote: [/ref]
    [...]example 
    >
    > You can't reference aliases in the field list or Where clauses.
    >
    > You can in the Having and Order By though.
    >
    > Tigger[/ref]

    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 Guest

  4. #4

    Default Re: Question about aliasing


    Stan R. wrote: 

    It does, although I usually use JOINs instead

    Captain Guest

  5. #5

    Default Re: Question about aliasing

    com says... 
    >
    > It does, although I usually use JOINs instead[/ref]

    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 Guest

  6. #6

    Default Re: Question about aliasing


    Geoff Muldoon wrote:
     
    > >
    > > It does, although I usually use JOINs instead[/ref]
    >
    > Oracle's
    > WITH <subquery> SELECT
    > and
    > FROM <subquery>
    > allow you to do some things that simple ANSI joins just can't match.
    >
    > Geoff M[/ref]

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

    Captain Guest

Similar Threads

  1. Anti-aliasing 3D
    By Paul Neave in forum Macromedia Director 3D
    Replies: 9
    Last Post: February 11th, 06:21 PM
  2. Anti-aliasing!!
    By frank in forum Macromedia Director 3D
    Replies: 0
    Last Post: September 15th, 07:04 PM
  3. Aliasing and Inheritance
    By Robert Klemme in forum Ruby
    Replies: 2
    Last Post: July 31st, 10:01 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139