INNER JOIN and GROUP BY

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

I think for your original query to work you would need to join your two tables on a Unique key, which I think explains why it did not work. Since code is not a unique value in ordersb it cannot join the records and so it adds the value of each record to each record producing twice as many records for the sum calculation to work with. If your tables have a unique key ID then joining on that should produce the correct results using your first query.

Please clarify with an example what you are trying to achieve.

Nestorio,
I believe sal1150 is trying to get the results of a sum of ordersb.qty minus a sum of orderss.qty grouped by ordersb.code, which should result in this:

code qty
------------------------------------
1010 100
1030 250

But being that he does not have a Unique ID field in his tables he can't get with the query that he posted, since no inner join is possible.

My example above works in that it creates a unique field for each table in seperate queries and then does the calculation. Can you give an example of how he could do it in a single query?

Sam

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Or:

SELECT q1.code, q1.sum1, q2.sum2, (q1.sum - q2.sum) as DiffSum
FROM [SELECT code, sum(qty) AS sum1
FROM orderss
GROUP BY code;
]. as q1 INNER JOIN [SELECT code, sum(qty) AS sum2
FROM ordersb
GROUP BY code;
]. as q2 ON q1.code = q2.code;

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nestorio,
ThanX for the wisdom! I knew it could be done in a single query but did not know how. I had to make a change to your second statement though:

SELECT q1.code, q1.sum1, q2.sum2, [Sum2]-[Sum1] AS DiffSum
FROM [SELECT code, sum(qty) AS sum1
FROM orderss
GROUP BY code;
]. AS q1 INNER JOIN [SELECT code, sum(qty) AS sum2
FROM ordersb
GROUP BY code;
]. AS q2 ON q1.code = q2.code;

Sam

Thanks Sam.

While you guys make me look like a novice using all my multiple queries, it's pretty cool to learn how to do these the right way. ThanX Shane & Nestorio!

Sam, don't worry. I learnt that here at EE. And your comments are always very good.

No probs Sam :) You might want to whack an Nz in there to take into account records where no matching records appear in the second table though.

ASKER

I tray to use the sql of shanesuebsahakarn
but I get this error -Im using DBISAM-

dbisam engine error # 11949 sql error- 'right parentheses expected.instead found 'sum''
in source column expression

AND THIS IS MY SQL:-

SELECT ordersb.code,
Sum([ordersb.quantity]) - (SELECT sum([orderss.quantity]) FROM orderss WHERE orderss.code=ordersb.code)
FROM ordersb
GROUP BY code

Try this:
SELECT ordersb.code,
Sum([ordersb].[quantity]) - (SELECT sum([orderss].[quantity]) FROM orderss WHERE orderss.code=ordersb.code)
FROM ordersb
GROUP BY code

ASKER

SAME PROBLEM...
It is working fine when on ms access but in DBISAME no working.

Hmm, looks like your back end database doesn't support subqueries.

Try using will_scarlet7's method (the second post in this thread).

ASKER

will_scarlet7
You are ok... but how can I change my tables to include them with Unique ID field ?

Sal1150,
First, did you try my suggestion using 3 individual queries? That may not work any better that Shane's since it is similar, but it might since the queries are split up and not nested. That would be simpler since it creates a unique ID for both tables by grouping them individualy on the field "code", so that each table only has one instance of "Code" to link to in the other table.

God bless!

Sam

Sal1150,
After toying with it more I would recommend if the above does not work that you create a function that calculates the sum of ordersb.qty - the sum of orderss.qty by "code" like this:

Function GetQtySum(strCode As String) As Integer
Dim rs As Object
Dim strSQL As String
Set rs = CreateObject("Adodb.Recordset")
strSQL = "SELECT * FROM ordersb WHERE ordersb.code='" & strCode & "';"
rs.Open strSQL, CurrentProject.Connection, 1
If rs.BOF And rs.EOF Then
GetQtySum = 0
Else
Do While Not rs.EOF
GetQtySum = GetQtySum + rs("qty")
rs.MoveNext
Loop
End If
rs.Close
strSQL = "SELECT * FROM orderss WHERE orderss.code='" & strCode & "';"
rs.Open strSQL, CurrentProject.Connection, 1
If Not rs.BOF And Not rs.EOF Then
Do While Not rs.EOF
GetQtySum = GetQtySum - rs("qty")
rs.MoveNext
Loop
End If
Set rs = Nothing
End Function

You can then create a query that works like this:

SELECT tblCodes.Code, GetQtySum([Code]) AS Quantity
FROM tblCodes;

(Note: the query syntax above is based on the assumption that you have a seperate table that lists all the codes used in your ordering system [tblCodes])

Another assumption... The function posted above is based on the assumption that your Code field is text. However I realise that it is likely that it is a numeric field and if so the function would need to be modified like this:

Function GetQtySum(myCode As Integer) As Integer
Dim rs As Object
Dim strSQL As String
Set rs = CreateObject("Adodb.Recordset")
strSQL = "SELECT * FROM ordersb WHERE ordersb.code=" & myCode & ";"
rs.Open strSQL, CurrentProject.Connection, 1
If rs.BOF And rs.EOF Then
GetQtySum = 0
Else
Do While Not rs.EOF
GetQtySum = GetQtySum + rs("qty")
rs.MoveNext
Loop
End If
rs.Close
strSQL = "SELECT * FROM orderss WHERE orderss.code=" & myCode & ";"
rs.Open strSQL, CurrentProject.Connection, 1
If Not rs.BOF And Not rs.EOF Then
Do While Not rs.EOF
GetQtySum = GetQtySum - rs("qty")
rs.MoveNext
Loop
End If
Set rs = Nothing
End Function

ASKER

Ok can you tell how to creat database system for buy orders and sill orders.
I want three tables one for buy orders ,one for sill orders and one for company data.

There was several valid replies to this question (as it was originally asked). I suggest a split points among will_scarlet7, shanesuebsahakarn and Nestorio.