Click here to Skip to main content
14,661,186 members
Rate this:
Please Sign up or sign in to vote.
See more:
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

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


What I have tried:

I try this but did not work (dividing on number of rows)
--Count(PS_DailyProduction.FK_OF)
    SUM(Case WHEN [FK_PSCategory]=2 then ((info.Length * info.[Quantity] )/1000)Else 0 end ) /Count(PS_DailyProduction.FK_OF)as 'm'
Posted
Updated 11-Sep-20 4:00am
v2
Comments
Maciej Los 11-Sep-20 7:13am
   
Can you elaborate this part: "half quantity on one day and the rest onther day"? Do you need sum of it or average (or else)?

Rate this:
Please Sign up or sign in to vote.

Solution 1

Try using GROUP BY: SQL GROUP BY Statement- w3Schools[^]
   
Comments
Mahfoud Bouabdallah 11-Sep-20 5:30am
   
@OriginalGriff I try it did not work
OriginalGriff 11-Sep-20 6:03am
   
"It doesn't work" is probably the most useless problem report we get - and we get it a lot. It tells us nothing about what is happening, or when it happens.
So tell us what it is doing that you didn't expect, or not doing that you did.
Tell us what you did to get it to happen.
Tell us any error messages.

Show us what you tried.
Rate this:
Please Sign up or sign in to vote.

Solution 2

With help of Akina - Database Administrators Stack Exchange[^]
This is how I did it
   SELECT 
  info.[OFNumber] 
  --Get sum of item quantity from the same category (all quantity before production) PS_ProjectInfo table
  ,(SELECT SUM(CASE WHEN (PS_ProjectInfo.[FK_PSCategory]=2 )THEN CAST(((CAST(PS_ProjectInfo.Length AS float) * PS_ProjectInfo.[Quantity])/1000)AS decimal(18,2)) ELSE 0 END) 
      FROM PS_ProjectInfo WHERE PS_ProjectInfo.OFNumber=info.[OFNumber] GROUP BY PS_ProjectInfo.OFNumber) 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]
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100