unable to use aliases in SELECT

Say I have the following table / rows:

---------------------------------------

CREATE TABLE test (
stock_num VARCHAR(25) DEFAULT '',
ordered INT(8) DEFAULT '0',
received INT(8) NULL DEFAULT NULL
);

INSERT INTO test VALUES ('a', 2, 4);
INSERT INTO test (stock_num, ordered) VALUES ('a', 1);

---------------------------------------

Now say that I try to run the following:

---------------------------------------

SELECT stock_num,
ordered - COALESCE(received, 0) AS subtotal,
SUM(CASE
WHEN subtotal < 0 THEN 0
ELSE subtotal
END) AS total
FROM test
GROUP BY stock_num;

---------------------------------------

Why do I get a "Unknown column 'subtotal' in 'field list'" when I do
that? Replacing subtotal, in the CASE, with what it's actually equal
to yields the results that I want, but it seems less efficient... why
require MySQL do the math twice when it could, instead, do it once?

yawnmoth <terra1024@yahoo.com> wrote in news:1189712903.012872.235200@
50g2000hsm.googlegroups.com:

> Say I have the following table / rows:
>
> ---------------------------------------
>
> CREATE TABLE test (
> stock_num VARCHAR(25) DEFAULT '',
> ordered INT(8) DEFAULT '0',
> received INT(8) NULL DEFAULT NULL
> );
>
> INSERT INTO test VALUES ('a', 2, 4);
> INSERT INTO test (stock_num, ordered) VALUES ('a', 1);
>
> ---------------------------------------
>
> Now say that I try to run the following:
>
> ---------------------------------------
>
> SELECT stock_num,
> ordered - COALESCE(received, 0) AS subtotal,
> SUM(CASE
> WHEN subtotal < 0 THEN 0
> ELSE subtotal
> END) AS total
> FROM test
> GROUP BY stock_num;
>
> ---------------------------------------
>
> Why do I get a "Unknown column 'subtotal' in 'field list'" when I do
> that? Replacing subtotal, in the CASE, with what it's actually equal
> to yields the results that I want, but it seems less efficient... why
> require MySQL do the math twice when it could, instead, do it once?

What if you change it to

SELECT stock_num,
ordered - COALESCE(received, 0) AS subtotal,
SUM(CASE
WHEN ordered < 0 THEN 0
ELSE ordered
END) AS total
FROM test
GROUP BY stock_num;

On Sep 13, 3:48 pm, yawnmoth <terra1...@yahoo.com> wrote:
> Say I have the following table / rows:
>
> ---------------------------------------
>
> CREATE TABLE test (
> stock_num VARCHAR(25) DEFAULT '',
> ordered INT(8) DEFAULT '0',
> received INT(8) NULL DEFAULT NULL
> );
>
> INSERT INTO test VALUES ('a', 2, 4);
> INSERT INTO test (stock_num, ordered) VALUES ('a', 1);
>
> ---------------------------------------
>
> Now say that I try to run the following:
>
> ---------------------------------------
>
> SELECT stock_num,
> ordered - COALESCE(received, 0) AS subtotal,
> SUM(CASE
> WHEN subtotal < 0 THEN 0
> ELSE subtotal
> END) AS total
> FROM test
> GROUP BY stock_num;
>
> ---------------------------------------
>
> Why do I get a "Unknown column 'subtotal' in 'field list'" when I do
> that? Replacing subtotal, in the CASE, with what it's actually equal
> to yields the results that I want, but it seems less efficient... why
> require MySQL do the math twice when it could, instead, do it once?

How do you know that MySQL is doing the math twice? Have you looked
at the code?