Unable to use subquery into group by

Question

I get this error:

Msg 144, Level 15, State 1, Line 68
Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

when running this code:

SELECT 
    CASE 
        WHEN O.Id IS NULL
            THEN 0
            ELSE O.Id
    END AS TabellaOrdiniId,
    OM.ID AS IdOrdineMexal,
    OM.csg_doc + RIGHT('00' + CONVERT(VARCHAR(2), OM.NGB_SR_DOC), 2) + '-' + RIGHT('000000' + CONVERT(VARCHAR(6), OM.NGL_DOC), 6) AS OrdineMadre,
    CASE 
        WHEN O.Id IS NULL
            THEN (  -- >>>look here: *** <<<
                  SELECT TOP 1 [dbo].[vAP_StatiOrdine].Id
                  FROM [dbo].[vAP_StatiOrdine]
                  WHERE [dbo].[vAP_StatiOrdine].Attivo = 1
                    AND [dbo].[vAP_StatiOrdine].Id = CONVERT(INT, ISNULL((SELECT TOP 1 [dbo].[tParametri].[ValoreParametro]
                                    FROM [dbo].[tParametri]
                                    WHERE [dbo].[tParametri].ChiaveParametro = 'IdStatoOrdineDiDefaultOrdini'), 0))
                    )
        ELSE O.IdStatoOrdine
    END AS IdStatoOrdine,
    SUM(OD.[PrezzoDiVenditaTotaleNonIvato]) AS TotalePrezzoDiVenditaNonIvatoOrdine
FROM 
    dgw_rp..DGW_ORDC_M AS OM
LEFT JOIN 
    dgw_rp..DGW_RUDT AS C ON C.CKY_CNT = OM.CKY_CNT_CLFR
LEFT JOIN 
    dgw_rp..dgw_ISPED AS ISPED ON ISPED.CKY_INDIR_ALT = OM.CKY_SPED_COD
LEFT JOIN 
    tOrdini AS O ON O.IdOrdineMexal = OM.ID
LEFT JOIN 
    tStatiOrdine AS SO ON SO.Id = O.IdStatoOrdine
LEFT JOIN 
    tTipiIncasso AS TI ON TI.Id = O.IdTipoIncasso
LEFT JOIN 
    tTipiServiziCorrieri AS TS ON TS.Id = O.IdTipoServizio
LEFT JOIN 
    tTipiBolla AS TB ON TB.Id = O.IdTipoBolla
LEFT JOIN 
    vAP_OrdiniDettagli AS OD ON OD.IdOrdineMexal = OM.ID
LEFT JOIN 
    tAnomalieOrdiniByCorriere AS AOC ON AOC.IdTabellaOrdini = O.Id
GROUP BY 
    CASE 
        WHEN O.Id IS NULL
            THEN 0
            ELSE O.Id
    END, OM.ID,
    OM.csg_doc + RIGHT('00' + CONVERT(VARCHAR(2), OM.NGB_SR_DOC), 2) + '-' + RIGHT('000000' + CONVERT(VARCHAR(6), OM.NGL_DOC), 6),
    CASE 
        WHEN O.Id IS NULL
            THEN (
                    -- >>>look here: *** <<<
                    SELECT TOP 1 [dbo].[vAP_StatiOrdine].Id
                    FROM [dbo].[vAP_StatiOrdine]
                    WHERE [dbo].[vAP_StatiOrdine].Attivo = 1
                        AND [dbo].[vAP_StatiOrdine].Id = CONVERT(INT, isnull((
                                    SELECT TOP 1 [dbo].[tParametri].[ValoreParametro]
                                    FROM [dbo].[tParametri]
                                    WHERE [dbo].[tParametri].ChiaveParametro = 'IdStatoOrdineDiDefaultOrdini'
                                    ), 0))
                    )
        ELSE O.IdStatoOrdine
    END

The problem seems to be the subquery. Also in field set and group by clause.

But I don't understand why: maybe because the query engine can't recognize TOP 1 and "believes" that the subquery returns more than one value.

If I replace the subquery with a scalar function, or an integer constant, everything works.

Can anyone explain me why I can't use subquery in group by clause?

Answer 1

The first subquery seems fine. The issue is with the second subquery which is inside the GROUP BY clause. Try like this inside the GROUP BY clause

GROUP BY 
O.Id, OM.ID, OM.csg_doc, OM.NGB_SR_DOC, OM.NGL_DOC

Or, you can try getting the values which are summed to a temp table or table variable and join it to the final result. This depends on the data you select