When I try to get MIN value of manufactured parts I get (Only one expression ... when the subquery is not introduced with EXISTS)

Question

I try to get MIN value of manufactured parts grouped by project like so:

This is my query:

SELECT 
   proinfo.ProjectN 
  ,ProjShipp.[Parts] 
  ,ProjShipp.Qty AS 'Qty Total'
  ,Sum(DailyProduction.Quantity) AS 'Qty Manufactured'

  ,(SELECT  DailySumPoteau.IdProject, MIN(DailySumPoteau.DailySum) 
   FROM  (SELECT PShipp.IdProject,  SUM(DailyWelding.Quantity) DailySum  
          FROM   DailyWeldingPaintProduction DailyWelding 
          INNER JOIN ProjectShipping  PShipp ON PShipp.id=DailyWelding.FK_idPartShip  
          WHERE PShipp.id=ProjShipp.id 
          GROUP BY PShipp.id,PShipp.IdProject)DailySumPoteau  
    GROUP BY DailySumPoteau.IdProject ) AS 'Qt Pole'

 FROM [dbo].[DailyWeldingPaintProduction]  DailyProduction
 INNER join ProjectShipping ProjShipp on ProjShipp.id=DailyProduction.FK_idPartShip
 inner join ProjectInfo proinfo on proinfo.id=IdProject

  GROUP By proinfo.id
  ,proinfo.ProjectN 
  ,ProjShipp.[Parts] 
  ,ProjShipp.Qty
  ,ProjShipp.[Designation] 
  ,ProjShipp.id

I have three tables:

• 01 - ProjectInfo: it stores information about the project:

• 02 - ProjectShipping: it stores information about the parts and it has ProjectInfoId as foreign key:

• 03 - DailyWeldingPaintProduction: it stores information about daily production and it has ProjectShippingId as foreign key:

but when I run it I get this error:

Msg 116, Level 16, State 1, Line 13
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

How can I solve this problem?.

Answer 1

From your target results, I suspect that you want a window MIN(). Assuming that your query works and generates the correct results when the subquery is removed (column QtPole left apart), that would be:

SELECT pi.ProjectN, ps.[Parts], ps.Qty AS QtyTotal,
    SUM(dp.Quantity) AS QtyManufactured,
    MIN(SUM(dp.Quantity)) OVER(PARTITION BY pi.ProjectN) AS QtPole
    ps.Designation
FROM [dbo].[DailyWeldingPaintProduction]  dp
INNER join ProjectShipping ps on ps.id=dp.FK_idPartShip
INNER join ProjectInfo pi on pi.id=IdProject
GROUP BY pi.id, pi.ProjectN, ps.[Parts], ps.Qty, ps.Designation, ps.id

Side note: don't use single quotes for identifiers; they should be reserved for literal strings only. Use the proper quoting character for your database (in SQL Server: square brackets) - or better yet, use identifiers that do not require being quoted.