Problem with SQL JOIN calculation on SUM

syedmeesamali

Member
Joined
Jan 16, 2020
Messages
19
Programming Experience
5-10
Hi All,
I am have three tables in my database i.e. Stockin, Stockout and Products. Below is their detailed schema.
Products:
CREATE TABLE [dbo].[Products](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Prod_ID] [varchar](10) NOT NULL,
    [Prod_Name] [varchar](150) NOT NULL,
    [Re_Order] [float] NOT NULL,
PRIMARY KEY CLUSTERED

Stockin:
CREATE TABLE [dbo].[StockinTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Date] [date] NOT NULL,
    [Sup_ID] [varchar](10) NOT NULL,
    [Sup_Name] [varchar](50) NOT NULL,
    [Prod_ID] [varchar](10) NOT NULL,
    [Prod_Name] [varchar](100) NOT NULL,
    [Expiry] [date] NOT NULL,
    [Units] [float] NOT NULL,
    [Cost] [float] NOT NULL,
PRIMARY KEY CLUSTERED

and Stockout as below:
Stockout:
CREATE TABLE [dbo].[StockoutTable](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Date] [date] NOT NULL,
    [Invoice] [varchar](12) NOT NULL,
    [Cust_Name] [varchar](150) NOT NULL,
    [Prod_ID] [varchar](10) NOT NULL,
    [Prod_Name] [varchar](150) NOT NULL,
    [Boxes] [float] NOT NULL,
    [Pcs] [float] NOT NULL,
    [Price] [float] NOT NULL,
PRIMARY KEY CLUSTERED

Now the problem is when I try to use the below SQL query everything is fine (in terms of calculation):
First SELECT - JOIN - GROUP BY:
SELECT pr.Prod_ID, pr.Prod_Name, sum(sin.Units) as [Units] FROM Products as pr
JOIN StockinTable AS sin ON (pr.Prod_Name = sin.Prod_Name)
WHERE pr.Prod_Name = 'CIPOLE FRESCO 5KG'

But when I update my Query to include the Stockout table in JOIN then the calculation is terribly wrong as the sum is calculated for each time there is an occurrence of an item in stockout table. Any solution or fixes? Below is erroneous calculation (or JOIN).
Second SELECT - JOIN - GROUP BY (WRONG ONE):
SELECT pr.Prod_ID, pr.Prod_Name, sum(sin.Units) as [Units] FROM Products as pr
JOIN StockinTable AS sin ON (pr.Prod_Name = sin.Prod_Name)
JOIN StockoutTable AS sout ON (pr.Prod_Name = sout.Prod_Name)
WHERE pr.Prod_Name = 'CIPOLE FRESCO 5KG'
GROUP BY pr.Prod_ID, pr.Prod_Name

As an example the first SQL statement returns value of 6025 in SUM which is CORRECT but the second one jumps the value to 72300 which is grossly incorrect.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,813
Location
Sydney, Australia
Programming Experience
10+
First things first, it looks to me like both your Stockin and Stockout tables should not have those Prod_ID and Prod_Name columns but, rather, should have a foreign key to the Id column in the Products table. That is unless those tables refer to stock for which there is no record in the Products table.
 

syedmeesamali

Member
Joined
Jan 16, 2020
Messages
19
Programming Experience
5-10
Thanks for the response. Would really appreciate if you can guide me how to make the change easily in my current schema as I really want to get it done asap. Any help is appreciated.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,813
Location
Sydney, Australia
Programming Experience
10+
Why are you trying to include the StockoutTable in the first place? You're not using it anywhere else in the query so why is it there at all? The first query does what you want so why are you not just using the first query?

In fact, given your current schema, why are you even using Products? You are filtering by Prod_Name but you have that column in StockinTable so that table is all you need:
SQL:
SELECT Prod_ID, Prod_Name, SUM(Units) AS [Units]
FROM StockinTable
WHERE Prod_Name = 'CIPOLE FRESCO 5KG'
The fact that you are joining to Products at all is evidence that you should not have that Prod_Name column in StockInTable and the fact that you are joining against other than the primary key is evidence that your schema is just plain bad.
 

syedmeesamali

Member
Joined
Jan 16, 2020
Messages
19
Programming Experience
5-10
My target is to get "Purchase Cost" from "Stockin table" and "Sales Price along with Invoice Number" from the "Stockout table". These are completely different tables except for the product ID and Name columns. Yes I made mistake of not properly defining the schema and of not reinforcing "FOREIGN KEY" constraints. Currently working to fix the mess.
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,813
Location
Sydney, Australia
Programming Experience
10+
Why do you think that this is something that should be possible with a single query? If you want information about stock in then get it from the StockinTable and if you want information about stock out then get it from the StockoutTable. If that requires two queries then use two queries.

There's no way to create a 1:1 correspondence between records in one table and record in the other because there is no such relationship. You'd have to have information about individual items, not just what product they were, e.g. if you had serial numbers for each item. Joins are always going to multiply the number of rows. If the inherent relationship is 1:1 then the product is 1. If the relationship is many:many, as it is with your two tables, then you're multiplying many by many and getting many more. If you're not interested in every combination the a join is not what you want.
 

syedmeesamali

Member
Joined
Jan 16, 2020
Messages
19
Programming Experience
5-10
Okay here is the deal. I want to get the "Current stock" information which will be result from stockin and stockout. There is some quantity purchased (stockin) and then some quantities sold (for each item) saved in (stockout). To get "Current stock position" I need to sum the stockin quantity and subtract the stockout quantities? What I believe (maybe wrongly so) is that using joins and sums will give me current stock position based on data from these two tables?
Also another purpose of joins is to show SUMMARY of all the data (stockin and stockout) in one single output table. Ain't it possible?
 

jmcilhinney

C# Forum Moderator
Staff member
Joined
Apr 23, 2011
Messages
3,813
Location
Sydney, Australia
Programming Experience
10+
To get "Current stock position" I need to sum the stockin quantity and subtract the stockout quantities?
Yeah, that's right. Read what you wrote. You do the summing first and then the subtraction. That means two queries to get the two sums and then a subtraction. You can do the subtraction in SQL if you want but you still have to have two subqueries executed first. You do a single subtraction at the end, not one on every pair of records.
Also another purpose of joins is to show SUMMARY of all the data (stockin and stockout) in one single output table. Ain't it possible?
It's probably possible but you may want to actually define what you mean by "summary" rather than expecting us to assume. Whatever it is, it almost certainly won't be done by joining. The most likely candidate seems to be a union of the results of two queries and then sorting by Date.
 
Last edited:

syedmeesamali

Member
Joined
Jan 16, 2020
Messages
19
Programming Experience
5-10
Yeah, that's write. Read what you wrote. You do the summing first and then the suntraction. That means two queries to get the two sums and then a subtraction. You can do the subtraction in SQL if you want but you still have to have two subqueries executed first. You do a single subtraction at the end, not one on every pair of records.
Okay really thanks for this idea. Gonna check it.

It's probably possible but you may want to actually define what you mean by "summary" rather than expecting us to assume. Whatever it is, it almost certainly won't be done by joining. The most likely candidate seems to be a union of the results of two queries and then sorting by Date.
Summary will be "Prod ID, Prod Name, Date, Expiry, Stockin Units, Stockout Units, Re-Order, Current Stock". Thanks for valuable ideas. Gonna put them in action.
 

syedmeesamali

Member
Joined
Jan 16, 2020
Messages
19
Programming Experience
5-10
Okay after lot of study and online searches I am very happy to find solution. Thanks JMC for giving me direction as I have to do a "SUB-QUERY" to pull-out the results. Final (correct) query is as below.

Correct query:
SELECT sin.Prod_ID, sin.Prod_Name, sin.Bought, ISNULL(sout.SOLD,0) as [Sold],
ISNULL((sin.Bought - sout.SOLD),0) AS [Stock Now]
FROM (SELECT Prod_ID, Prod_Name, ISNULL(SUM(Units),0) AS Bought
    FROM StockinTable sout GROUP BY Prod_ID, Prod_Name) sin
        LEFT JOIN  (SELECT Prod_ID, ISNULL(SUM(Boxes),0) AS SOLD
        FROM StockoutTable GROUP BY Prod_ID) sout
     ON sin.Prod_ID = sout.Prod_ID
WHERE sin.Prod_Name = 'CIPOLE FRESCO 5KG'
 
Top Bottom