Re: Newbie Help

Hi,
 yes, that happens..
I forgot that assignment_type is in the original table and in the inner view.
You should use an alias for the tables like in the following statement :

 select a.assignment_type, a.project_identifier, p.description,  a.hours_allocated,

      sum_view.total_hours
 from assignments a, projects p,
  (select assignment_type,sum(hours_allocated)total_hours

     from assignments group by assignment_type) sum_view  where a.project_identifier = p.project_identifier   and a.assignment_type = sum_view.assignment_type ;

Hope this helps (I didn't try it)

    Dirk

"Rocky" <peawet08_at_hotmail.com> schrieb im Newsbeitrag news:3dc7f2f2$0$28311$fa0fcedb_at_lovejoy.zen.co.uk...
> Dirk,
>
> Thanks for your help, i understand what you are trying to do, however:
>
> I have tried the statement and although the inner query works on its own.
I
> am getting error messages saying the Column ambiguously defined.
>
> I have as the AS keyword before total_hours and the error changes to SQL
> command not properly ended.
>
> What can be wrong.
>
> Rocky
>
>
> "Dirk Tschentscher" <dirk.tschentscherREM_THIS_at_volkswagen.de> wrote in
> message news:aq8ki4$c2b1_at_doiweb4.volkswagen.de...
> > Hi Rocky,
> >
> > If I understand you in the right way, then this should work :
> >
> > select assignment_type, assignments.project_identifier, description,
> > hours_allocated,
> > sum_view.total_hours
> > from assignments, projects,
> > (select assignment_type,sum(hours_allocated)total_hours
> > from assignments group by assignment_type) sum_view
> >
> > where assignments.project_identifier = projects.project_identifier
> > and assignments.assignment_type = sum_view.assignment_type ;
> >
> > rgds
> > Dirk
> >
> >
> > "Rocky" <peawet08_at_hotmail.com> schrieb im Newsbeitrag
> > news:3dc7d1d1$0$28312$fa0fcedb_at_lovejoy.zen.co.uk...
> > I am just starting to learn SQL and am stuck on a question in my
tutorial.
> >
> > I have used the following:
> >
> > select assignment_type, assignments.project_identifier, description,
> > hours_allocated
> > from assignments, projects
> > where assignments.project_identifier = projects.project_identifier;
> >
> > to retreive the following:
> >
> > AS PROJECT_IDENTIFIER DESCRIPTION HOURS_ALLOCATED
> > -- ------------------ ------------------------- ---------------
> > C3 1433 Results 9
> > C3 1433 Results 4
> > A1 2179 Student Tracking System 3
> > A1 2179 Student Tracking System 7
> > B2 2265 Staff Time Tabling 7
> > B2 2265 Staff Time Tabling 5
> > B2 2265 Staff Time Tabling 6
> > B2 3144 Room Time Tabling 6
> > A1 4134 Computerised Registers 5
> > A1 4134 Computerised Registers 8
> > A1 4134 Computerised Registers 9
> >
> > AS PROJECT_IDENTIFIER DESCRIPTION HOURS_ALLOCATED
> > -- ------------------ ------------------------- ---------------
> > C3 6588 Course Developments 10
> > C3 6588 Course Developments 8
> >
> > My problem is that I need to display in another colunm, the total hours
> for
> > each assignment type.
> >
> > Can anybody please show me the easiest way to do this.
> >
> > Thanks
> >
> > Rocky
> >
> >
>
>
Received on Wed Nov 06 2002 - 03:53:18 CST