Re: Newbie Help

This answers my question

Thank you

Rocky

"Alan Mills" <Alan.Mills_at_nospamservices.fujitsu.com> wrote in message news:aq8ng7$eho$1_at_news.icl.se...
>
> "Rocky" <peawet08_at_hotmail.com> wrote in message
> news:3dc7dfb3$0$28315$fa0fcedb_at_lovejoy.zen.co.uk...
> > Surely there is an easier way of doing this.
> >
> > It seems realy complicated based on what I have covered so far
> >
> > Cheers
> >
> > 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
> > >
>
> there is no easier way if you want to keep all the columns you have so
far.
> If you would sacrifice the indivdual hours_allocated column in your result
> set for the total then the solution is somewhat easier, like
>
> select assignment_type, assignments.project_identifier, description,
> SUM(hours_allocated)
> from assignments, projects
> where assignments.project_identifier = projects.project_identifier
> GROUP BY assignment_type, assignments.project_identifier, description
>
> which sounds more on par where you might be in your coursework.
>
> Of course each, assignment type will only be listed once, with its total
> rather than a copy for each instance as Dirk was trying to preserve.
>
> If this doesn't answer the question then I would suggest you explain what
> you're looking for in a little more detail.
>
> >
>
>
Received on Tue Nov 05 2002 - 09:44:19 CST