Solved

INNER JOIN and GROUP BY

Posted on 2004-09-03
26
719 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
Comment Utility
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
Comment Utility
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
Comment Utility
Please clarify with an example what you are trying to achieve.
0
 
LVL 15

Expert Comment

by:will_scarlet7
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Sam.
0
 
LVL 15

Expert Comment

by:will_scarlet7
Comment Utility
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
Comment Utility
Sam, don't worry. I learnt that here at EE. And your comments are always very good.
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
SAME PROBLEM...
It is working fine when on ms access but in DBISAME no working.
0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I'm OK with Nestorio's proposal...
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

744 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now