INNER JOIN and GROUP BY

I have tow tables ordersb and orderss.

ordersb has the folowing recordes
------------------------------------
code                    qty
------------------------------------
1010                    600
1030                    300
1030                    100


orderss has the folowing recordes
------------------------------------
code                    qty
------------------------------------
1010                    500
1030                     50
1030                    100

Now I want to sum ordersb.qty and sum orderss.qty
I try this sql but the rsult not true.

select
ordersb.code,orderss.code,
SUM(ordersb.qty-orderss.qty) as quantity
FROM ORDERSb INNER JOIN ORDERSs ON ORDERSb.code=ORDERSs.code
GROUP BY ORDERSb.code

sal1150Asked:
Who is Participating?
 
will_scarlet7Commented:
I'm not sure why it does not work, but I was able to find a working workaround, which was to create 3 queries like so:

Query_Ordersb:
SELECT ordersb.code, Sum(ordersb.qty) AS SumOfqty
FROM ordersb
GROUP BY ordersb.code;

Query_Orderss:
SELECT orderss.code, Sum(orderss.qty) AS SumOfqty
FROM orderss
GROUP BY orderss.code;

QueryQuantity:
SELECT Query_Ordersb.code, ([Query_Ordersb].[SumOfqty]-[Query_Orderss].[SumOfqty]) AS Quantity
FROM Query_Ordersb INNER JOIN Query_Orderss ON Query_Ordersb.code = Query_Orderss.code;

It would probably be possible for you to do the same thing in a single query using nested (sub) queries, but I don't have much experience with that.
0
 
will_scarlet7Commented:
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.
0
 
NestorioCommented:
Please clarify with an example what you are trying to achieve.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
will_scarlet7Commented:
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
0
 
NestorioCommented:
Sam,

SELECT q1.code, q1.sum1, q2.sum2
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;
0
 
NestorioCommented:
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;

0
 
shanesuebsahakarnCommented:
To do it in one query, I supposed you'd do something like this:

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

Since the code in ordersb and orderss will always be the same for matching records, you don't really need to select the code from both tables.
0
 
will_scarlet7Commented:
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
0
 
NestorioCommented:
Thanks Sam.
0
 
will_scarlet7Commented:
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!
0
 
NestorioCommented:
Sam, don't worry. I learnt that here at EE. And your comments are always very good.
0
 
shanesuebsahakarnCommented:
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.
0
 
sal1150Author Commented:
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
0
 
shanesuebsahakarnCommented:
Try this:
SELECT ordersb.code,
 Sum([ordersb].[quantity]) - (SELECT sum([orderss].[quantity]) FROM orderss WHERE orderss.code=ordersb.code)
FROM ordersb
 GROUP BY code
0
 
sal1150Author Commented:
SAME PROBLEM...
It is working fine when on ms access but in DBISAME no working.
0
 
shanesuebsahakarnCommented:
Hmm, looks like your back end database doesn't support subqueries.

Try using will_scarlet7's method (the second post in this thread).
0
 
sal1150Author Commented:
will_scarlet7
You are ok... but how can I change my tables to include them with Unique ID field ?
0
 
will_scarlet7Commented:
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
0
 
will_scarlet7Commented:
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])
0
 
will_scarlet7Commented:
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
0
 
sal1150Author Commented:
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.
 
0
 
NestorioCommented:
There was several valid replies to this question (as it was originally asked). I suggest a split points among will_scarlet7, shanesuebsahakarn and Nestorio.
0
 
will_scarlet7Commented:
I'm OK with Nestorio's proposal...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.