Incorrect sum values the quantity is multiplied by the number of rows

Question

0.00/5 (No votes)

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 10-Sep-20 23:03pm

Mahfoud Bouabdallah

Updated 11-Sep-20 4:00am

v2

Add a Solution

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)?

2 solutions

Solution 1

Try using GROUP BY: SQL GROUP BY Statement- w3Schools[^]

Posted 10-Sep-20 23:20pm

OriginalGriff

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.

Add a Solution

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

Mahfoud Bouabdallah · Accepted Answer · 2020-09-11T04:00:00

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]

Incorrect sum values the quantity is multiplied by the number of rows

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Incorrect sum values the quantity is multiplied by the number of rows

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us