SELECT statement - is this possible

Is this possible??

I have a table called "Jobs" with the following fields:

JobNum(key); Desc;Amount; JobType; PrimaryJob

JobType is either a 1 or 2; 1 indicates a primary contract job, 2 indicates a "change order" to an existing contract job. If the job is a primary contract, then primaryjob is the same as jobnum. If the job is a change order, then primary job is the jobnum of the primary contract

I would like to create a SELECT statement that gives me the following:

JobNum; Desc; Sum(amount) Where JobType = 1

but I want Sum(amount) to sum where PrimaryJob is the same.

Example: the following table would return:


JobNum; Desc; Amount; JobType; PrimaryJob
1 Bank 25000 1 1
2 Bakery 35000 1 2
3 change1 1000 2 1
4 change2 2000 2 1
5 change1 500 2 2


result:
1 Bank 28000
2 Bakery 35500

SELECT JobNum, [Desc], Sum(amount)
FROM Jobs
WHERE JobType = 1
GROUP BY JobNum, [Desc]

Note that Desc appears in [] because it is normally a Keyword to indicate Descending in an Order By statement of a query.

Thanks, but that won't sum on the PrimaryJob field. That only sums on the JobNum field (which is unique).

if I have the following records in the table

JobNum; [Desc]; Amount; JobType; PrimaryJob
1 Bank 25000 1 1
2 Bakery 35000 1 2
3 change1 1000 2 1
4 change2 2000 2 1
5 change1 500 2 2

then I would like the result to show

JobNum; [Desc]; sum(Amount)

1 Bank 28000 <-- this is sum of jobs 1, 3 & 4
2 Bakery 35500 <-- this is sum of jobs 2 & 5

SELECT Sum(Amount), PrimaryJob
FORM Jobs
GROUP BY PrimaryJob

Unfortunately, aggregates don't work that way. Because the Desc and JobNum fields are different, Change1(2, or 3) wouldn't be able to be lumped into the Bank or Bakery records.

Secondly, by your definition of the condition where JobType = 1, you wouldn't get 3, 4, or 5.

Thanks. I see what you're saying. I tried Davie's approach and it will give me the right sum, but if I try to add the [Desc] field into the statement, I lose the sum.

I only use SQL part time. Can a field in a Select statement be another select statement??

Something like;

Select JobNum, [Desc],(Select Sum(Amount) FROM JOBS GROUP BY PrimaryJob) FROM Jobs WHERE JobType =1 ....????

Well, you can have a nested SELECT statement but it doesn't serve as another "field"..the nested SQL statement is analyzed as another condition within the main SQL statement.