Re: Use of Column Alias in GROUP BY

From: Jan Nowitzky <nowitzky_at_informatik.uni-jena.de>
Date: 1998/12/15
Message-ID: <367653AD.95D06EFF@informatik.uni-jena.de>#1/1

GHouck wrote:

> In the following query, since it appears that you cannot
> use a column alias in either a GROUP BY or HAVING clause,
> is there avoidable/unavoidable overhead in referring to
> the Function SECTION(..) or COUNT(..) more than once?
>
> Are the results of the 'calls' cached to offset the
> multiple calls?
>
> SELECT
> SECTION(MAPNO) AS "SEC", COUNT(*) AS "KNT"
> FROM
> UTIL_POLES
> GROUP BY
> SECTION(MAPNO)
> HAVING
> COUNT(*) > 1
> ORDER BY
> KNT,SEC;
>
> Is there an alternative method?
>

Try
(1) SELECT
            SECTION(MAPNO) AS "SEC", COUNT(*) AS "KNT"     ...
    ORDER BY
          "KNT" , "SEC"; (2) SELECT
            SECTION(MAPNO) AS SEC, COUNT(*) AS KNT     ...
    ORDER BY
          KNT , SEC; Remark the quotas!

An alternative method is:

SELECT

          SECTION(MAPNO) AS "SEC", COUNT(*) AS "KNT"
        ...
     ORDER BY 1, 2;

Hope it helps!
Regards, Jan Received on Tue Dec 15 1998 - 00:00:00 CST