Re: sql select query with column 'AS' assignment

On Fri, 1 Feb 2002, DzZero wrote:

> DzZero wrote:
>
> > Several of these combinations work in MySQL, Access, and Oracle. Or at
> > least according to those online I have spoke to they do.
> >
> > Can any explain to me what I am doing wrong? If this is possible in
> > PostgreSQL? Or the proper way of doing this? Or even a source of
> > information that explains it. The closest source I found was obviously
> > the psql documentation but I have yet to find a specific example of what
> > I am doing.
> >
> > Thanks.
> >
>
> BTW.If I group by job_code on the last statement I posted it does so but
> it groups them as if job_code has the orginal values in it. Also I end
> up with something like:
>
> employee_id | first_name | last_name | date | sum | ?column?
> -------------+------------+------------+------------+-----+----------
> 7 | Larry | James | 2002-01-02 | 8 | f
>
>
> I'm lost. heh

IIRC the grouping happens on the stuff from the from,
not from the select list. If you want to do this, you'd probably need
a subselect in the from.

As for the above, the job_code='R' is a boolean expression (is job_code
equal to R?)
> > agi_timesheets=# select distinct
> > employee_id,first_name,last_name,date,sum(hours),job_code = 'R' from
> > timesheet group by employee_id,first_name,last_name,date having job_code
> > <> 'H' and job_code <> 'V' and date >= '01-01-2002' and date <=
> > '01-15-2002';
> > ERROR: Attribute timesheet.job_code must be GROUPed or used in an
> > aggregate function
> >

I'm not 100% sure what you're trying to get out, but maybe:
select employee_id, first_name, last_name, date, sum(hours), job_code
from
(select employee_id, first_name, last_name, date, hours, 'R' AS job_code
from timesheet where job_code<>'H' and job_code<>'V' and
date>='01-01-2002' and date<='01-15-2002'
) group by employee_id, first_name, last_name, date, job_code;