Thread: sql select query with column 'AS' assignment
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.
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
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_codefrom timesheet where job_code<>'H' and job_code<>'V'anddate>='01-01-2002' and date<='01-15-2002' ) group by employee_id, first_name, last_name, date, job_code;
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