Re: [sqlite] Referring to column alias

[Igor Tandetnik]

On 10/28/2011 4:28 PM, Pete wrote:

I have another variation of this issue:

SELECT col1 - col2 as Total, Total * price FROM tst

... gives an error " no such column: Total".  I can just repeat "col1 - col2"
of course, but wondering if there is a way to refer to Total within the
SELECT.


This is by design, blessed by SQL-92 standard. The closest you can get 
is something like


SELECT Total, Total * price FROM
(select col1 - col2 as Total, price from tst);

This will likely be noticeably slower though.
--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Referring to column alias

[Pete]

I have another variation of this issue:

SELECT col1 - col2 as Total, Total * price FROM tst

.. gives an error " no such column: Total".  I can just repeat "col1 - col2"
of course, but wondering if there is a way to refer to Total within the
SELECT.

Thanks,


Pete




> >
> > Message: 6
> > Date: Sun, 23 Oct 2011 21:02:07 +0200
> > From: Kees Nuyt 
> > To: sqlite-users@sqlite.org
> > Subject: Re: [sqlite] sqlite-users Digest, Vol 46, Issue 23
> > Message-ID: 
> > Content-Type: text/plain; charset=us-ascii
> >
> > On Sun, 23 Oct 2011 10:26:14 -0700, Pete 
> > wrote:
> >
> > >Apologies, I omitted what is the real cause of the problem.  This
> > simplified
> > >SELECT illustrates the error:
> > >
> > >SELECT sum( colc * cold ) as total from tst where total > 1000
> > >
> > >The error message is "misuse of aggregate: sum()".  No error if I remove
> > the
> > >where clause.
> >
> > A condition on an aggregate is expressed with a HAVING clause, not
> > a WHERE clause.
> >
> > That is because WHERE and HAVING work on different stages of the
> > SELECT statement: WHERE decides which rows to include in the
> > aggregate, HAVING decides which results to present after
> > aggregation.
> > --
> >  (  Kees Nuyt
> >  )
> > c[_]
> >
> >
> >
> > 
> >
> >5
> 
>
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users