Group By Problem

The following sql statement is giving error when i insert the line Group By...(to get the total amount of Job_id's):

SELECT Isnull(tbl1.Job_no, tbl2.Job_no) As Job_No, tbl1.Amount, tbl1.Entry_id
FROM tbl2 FULL OUTER JOIN tbl1
ON tbl1.colx = tbl2.coly
WHERE <Condition>
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);

tbl1 and tbl2 have columns Job_no. But one has a null value if the other value other that null. So the statement above will list the job_no (combined from the two tables), the Amount and the Entry_ID. What i'm trying to arrive at is to add all amount on the same Job_no.

any comment will be greatly appreciated.

Thanks!

Quote:

Originally Posted by Merio

The following sql statement is giving error when i insert the line Group By...(to get the total amount of Job_id's):

SELECT Isnull(tbl1.Job_no, tbl2.Job_no) As Job_No, tbl1.Amount, tbl1.Entry_id
FROM tbl2 FULL OUTER JOIN tbl1
ON tbl1.colx = tbl2.coly
WHERE <Condition>
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);

tbl1 and tbl2 have columns Job_no. But one has a null value if the other value other that null. So the statement above will list the job_no (combined from the two tables), the Amount and the Entry_ID. What i'm trying to arrive at is to add all amount on the same Job_no.

any comment will be greatly appreciated.

Thanks!

Instead of:
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);
Try
GROUP BY Job_No.

But i belive that that work either,
What you might have to do is group by all tthe other fields
GROUP BY tbl1.Amount, tbl1.Entry_id|||

Quote:

Originally Posted by tezza98

Instead of:
GROUP BY Isnull(tbl1.Job_no, tbl2.Job_no);
Try
GROUP BY Job_No.

But i belive that that work either,
What you might have to do is group by all tthe other fields
GROUP BY tbl1.Amount, tbl1.Entry_id

------------

The problem was solved when i changed the first line with this:
SELECT Isnull(tbl1.Job_no, tbl2.Job_no) As Job_no, SUM(tbl1.amount) As Amount

I needed to put the SUM on tbl1.amount. - I thought that tbl1.amount would be totaled automatically when GROUP BY is used... I was wrong. :0

Thanks for your comment :)