Sql-server – Incorrect Sum values the quantity is multiplied by the number of rows

sql server

I have this select query

   SELECT 
  info.[OFNumber] 
  --Get sum of item quantity from the same category (all quantity before production) PS_ProjectInfo table
  ,SUM(Case WHEN info.[FK_PSCategory]=2 then ((info.Length * info.[Quantity] )/1000)Else 0 end ) as 'm'
  
 -- Get sum of item quantity from the same category (Only quantity produced) PS_DailyProduction table
 ,SUM(Case WHEN info.[FK_PSCategory]=2 then PS_DailyProduction.Quantity ELSE 0 END) as 'qty'

FROM [dbo].[PS_DailyProduction] 
Left JOIN PS_ProjectInfo info on info.id=PS_DailyProduction.[FK_OF]

GROUP BY info.[OFNumber]

Now if each items has produced at once I have one row in my PS_DailyProduction table ,there is no problem, But whene any item has produced in two or more times (for example half quantity on one day and the rest onther day)her the problem show, in my PS_DailyProduction table i have more than one row and I get wrong result on

--Get sum of item quantity from the same category (all quantity before production) PS_ProjectInfo table
  SUM(Case WHEN info.[FK_PSCategory]=2 then ((info.Length * info.[Quantity] )/1000)Else 0 end ) as 'm'

The quantity is multiplied by the number of rows from PS_DailyProduction
I try this but did not work (dividing on number of rows)

--Count(PS_DailyProduction.FK_OF)
SUM(Case WHEN info.[FK_PSCategory]=2 then ((info.Length * info.[Quantity] )/1000)Else 0 end ) /Count(PS_DailyProduction.FK_OF)as 'm'

I use LEFT JOIN to get all rows from PS_ProjectInfo table even when they not produced yet

Best Answer

SELECT
info.[OFNumber] 
--Get sum of item quantity from the same category (all quantity before production) PS_ProjectInfo table
,SUM(Case WHEN info.[FK_PSCategory]=2 then ((info.Length * info.[Quantity] )/1000)Else 0 end ) as 'm'
  
-- Get sum of item quantity from the same category (Only quantity produced) PS_DailyProduction table
,SUM(Case WHEN info.[FK_PSCategory]=2 then p.Quantity ELSE 0 END) as 'qty'

FROM 
(
   SELECT [FK_OF], Sum(Quantity)
   FROM [dbo].[PS_DailyProduction]
   GROUP BY [FK_OF] 
) p
Left JOIN PS_ProjectInfo info on info.id=p.[FK_OF]

GROUP BY info.[OFNumber]

BTW, not quite understand why you use left join, since PS_ProjectInfo seems a must