SEO

#1 (**permalink**) 02-28-2008, 10:27 AM

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?

#2 (**permalink**) 02-28-2008, 10:27 AM

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;

#3 (**permalink**) 02-28-2008, 10:27 AM

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?

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

unable to use aliases in SELECT