using SUM with more then one table of data

[antonyx]

hi, this is quite urgent. i would need an answer within an hour if possible here is the situation



Details Table

CREATE TABLE Details (
ActivityId INT NOT NULL
CONSTRAINT Details_ActivityId_PK PRIMARY KEY,
deDescription VARCHAR2(40),
deDay VARCHAR2(10),
check (deDay in ('monday','tuesday','wednesday','thursday','friday')),
deHours INT NOT NULL,
Chargeable VARCHAR2(3)
Check (Chargeable in (‘yes’,’no’)));

Timesheet Table

CREATE TABLE Timesheet (
TimesheetId INT NOT NULL
CONSTRAINT Timesheet_TimesheetId_PK PRIMARY KEY,
tiWeekBeginning VARCHAR2(10),
ActivityId INT NOT NULL
CONSTRAINT Timesheet_ActivityId_FK
REFERENCES
Details(ActivityId),
CampaignId INT NOT NULL
CONSTRAINT Timesheet_CampaignId_FK
REFERENCES
Campaign(CampaignId),
StaffId INT NOT NULL
CONSTRAINT Timesheet_StaffId_FK
REFERENCES
Staff(StaffId));

with these two tables i wish to display the sum of the dehours as well as the timesheet id.
i can then link these sums to other tables. my prob is this

SQL> select sum(dehours),timesheetid
2 from details,timesheet;
select sum(dehours),timesheetid
*
ERROR at line 1:
ORA-00937: not a single-group group function

how can a sum be carried out and displayed on the oracle screen with details from another table it is linked too???

 

[PHV]

Something like this ?
SELECT TimesheetId, SUM(deHours)
FROM Timesheet INNER JOIN Details
ON Timesheet.ActivityId = Details.ActivityId
GROUP BY TimesheetId

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[antonyx]

wow that works thanks
ok what we would like to do with that now is this..
we have a campaign table, and the campaign id is in the timesheet table. how would we include a field such as CampaignName from the campaign table in this? would we need to use another join to the campaign table???