Not sure if this is the right newsgroup to use. I did not see a general 
one or sql statement one for postgres.  Please point me to the correct 
location if this is wrong and I apologize for the off topic if it is.


I am attempting to do a select in which I force an existing 
column(field) to a specific value. I need to do so in order to group 
data properly.

ie:
agi_timesheets=# select distinct 
employee_id,first_name,last_name,date,sum(hours),"R" as job_code 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 'R' not found

agi_timesheets=# select distinct 
employee_id,first_name,last_name,date,sum(hours),job_code AS "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

agi_timesheets=# select distinct 
employee_id,first_name,last_name,date,sum(hours),job_code AS '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:  parser: parse error at or near "'"

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


etc. etc. etc.  I have tried all possible combinations (or I think so) 
of "R", 'R', R using = or AS on either side of job_code.  Nothing seems 
to work.

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.


Fri, 01 Feb 2002 08:24:53 -0700
DzZero <spinzero@aero-graphics.com>
sql select query with column 'AS' assignment
DzZero wrote:

> Not sure if this is the right newsgroup to use. I did not see a general 
> one or sql statement one for postgres.  Please point me to the correct 
> location if this is wrong and I apologize for the off topic if it is.
> 
> 
> I am attempting to do a select in which I force an existing 
> column(field) to a specific value. I need to do so in order to group 
> data properly.
> 
> ie:
> agi_timesheets=# select distinct 
> employee_id,first_name,last_name,date,sum(hours),"R" as job_code 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 'R' not found
> 
> agi_timesheets=# select distinct 
> employee_id,first_name,last_name,date,sum(hours),job_code AS "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
> 
> agi_timesheets=# select distinct 
> employee_id,first_name,last_name,date,sum(hours),job_code AS '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:  parser: parse error at or near "'"
> 
> 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
> 
> 
> etc. etc. etc.  I have tried all possible combinations (or I think so) 
> of "R", 'R', R using = or AS on either side of job_code.  Nothing seems 
> to work.
> 
> 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


Fri, 01 Feb 2002 10:24:41 -0700
DzZero <spinzero@aero-graphics.com>
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;



Fri, 1 Feb 2002 11:33:54 -0800 (PST)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: sql select query with column 'AS' assignment
DzZero <spinzero@aero-graphics.com> writes:
> agi_timesheets=# select distinct 
> employee_id,first_name,last_name,date,sum(hours),"R" as job_code 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 'R' not found

"R" and 'R' are two quite different things: "R" is a name, 'R' is a
literal constant.  Not sure how many of your problems stem from lack
of understanding of this basic point, but quite a few of them do.

> agi_timesheets=# select distinct 
> employee_id,first_name,last_name,date,sum(hours),job_code AS "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

Isn't the error message clear enough?  You need to add job_code to
the GROUP BY list.

			regards, tom lane

Fri, 01 Feb 2002 15:01:10 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: sql select query with column 'AS' assignment 