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.
and Stockout as below:
Now the problem is when I try to use the below SQL query everything is fine (in terms of calculation):
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).
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.
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.