查询按项目分组的零件最小生产值遇Msg116错误的解决方法

Let's break down why you're hitting that Msg 116 error first:

The problem lies in your subquery for the 'Qt Pole' column. Right now, that subquery returns two columns (DailySumPoteau.IdProject and MIN(DailySumPoteau.DailySum)), but the outer query expects only a single value to populate the 'Qt Pole' column. SQL doesn't allow multiple columns in a scalar subquery (one used as a value in the select list) unless you're using EXISTS—which isn't the case here.

Since your goal is to get the minimum daily production sum per project, you don't need to include IdProject in that subquery's select list. I've also fixed a missing table alias in your ProjectInfo join (you had proinfo.id=IdProject which would throw another error, since IdProject belongs to ProjShipp).

Here's the corrected query:

SELECT 
    proinfo.ProjectN 
    ,ProjShipp.[Parts] 
    ,ProjShipp.Qty AS 'Qty Total' 
    ,Sum(DailyProduction.Quantity) AS 'Qty Manufactured' 
    ,(
        SELECT 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=ProjShipp.IdProject 
GROUP By 
    proinfo.id 
    ,proinfo.ProjectN 
    ,ProjShipp.[Parts] 
    ,ProjShipp.Qty 
    ,ProjShipp.[Designation] 
    ,ProjShipp.id

A quick extra tip: If you ever need to include the project ID alongside the minimum value later, consider refactoring this to use a JOIN with a CTE or derived table instead of a scalar subquery. This makes the query more readable and avoids issues with multiple columns in subqueries.

内容的提问来源于stack exchange，提问作者M.Bouabdallah