Re: Newbie Help

"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:18:30 CST