Solved

INNER JOIN and GROUP BY

Posted on 2004-09-03
26
740 Views
Last Modified: 2008-02-26
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

0
Comment
Question by:sal1150
  • 9
  • 6
  • 4
  • +1
26 Comments
 
LVL 15

Accepted Solution

by:
will_scarlet7 earned 168 total points
ID: 11971803
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11971836
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
 
LVL 16

Expert Comment

by:Nestorio
ID: 11971966
Please clarify with an example what you are trying to achieve.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11972024
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
 
LVL 16

Assisted Solution

by:Nestorio
Nestorio earned 166 total points
ID: 11972081
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
 
LVL 16

Expert Comment

by:Nestorio
ID: 11972102
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
 
LVL 41

Assisted Solution

by:shanesuebsahakarn
shanesuebsahakarn earned 166 total points
ID: 11972255
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11972291
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
 
LVL 16

Expert Comment

by:Nestorio
ID: 11972363
Thanks Sam.
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11972396
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
 
LVL 16

Expert Comment

by:Nestorio
ID: 11972440
Sam, don't worry. I learnt that here at EE. And your comments are always very good.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11972508
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
 

Author Comment

by:sal1150
ID: 11980485
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
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11980492
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
 

Author Comment

by:sal1150
ID: 11982222
SAME PROBLEM...
It is working fine when on ms access but in DBISAME no working.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 11982231
Hmm, looks like your back end database doesn't support subqueries.

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

Author Comment

by:sal1150
ID: 11982282
will_scarlet7
You are ok... but how can I change my tables to include them with Unique ID field ?
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11983526
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11983824
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 11983837
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
 

Author Comment

by:sal1150
ID: 12018627
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
 
LVL 16

Expert Comment

by:Nestorio
ID: 14127547
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
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 14128630
I'm OK with Nestorio's proposal...
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question