sql – Get the Minimum Value of 2 subqueries

I have a query which has 5 subqueries. I need it to have only 4 subqueries, since two of them are supposed to be only one result. The problem is I don’t know how to maintain the query structure and get the MIN value of 2 of those subqueries. This is my query:

Select Artigo, Descricao, sum(Stock) as Stock, Sum(QuantidadeEmEncomendasPendentes) as QuantidadeEmEncomendasPendentes, Sum(Stock) - Sum(QuantidadeEmEncomendasPendentes) As StockTotal
From (

    Select A1.Artigo as Artigo, A1.Descricao as Descricao, sum(AA.StkActual) as Stock, 0 as QuantidadeEmEncomendasPendentes, 'Artigo Stock' As Id
    From Artigo A1
        Inner Join ArtigoArmazem AA On AA.Artigo = A1.Artigo And (AA.Armazem IN ('A1','A5','A7','A8','A9'))
        and A1.CDU_DisponivelOnline = 1
    Group By A1.Artigo, A1.Descricao

    Union

    Select A1.Artigo as Artigo, A1.Descricao as Descricao, 0 as Stock, Sum(LD.Quantidade - LDS.QuantTrans) as QuantidadeEmEncomendasPendentes, 'Artigo Encomenda' As Id
    From Artigo A1
        Inner Join LinhasDoc LD On LD.Artigo = A1.Artigo And Year(LD.Data) >= 2018 And LD.Quantidade Is Not Null And LD.Quantidade > 0
        Inner Join LinhasDocStatus LDS On LDS.IdLinhasDoc = LD.Id  And LDS.Fechado = 0 And LDS.Quantidade > LDS.QuantTrans
        Inner Join CabecDocStatus CDS On CDS.IdCabecDoc = LD.IdCabecDoc And CDS.Fechado = 0 And CDS.Estado = 'P'
        Inner Join CabecDoc CD On CD.Id = LD.IdCabecDoc And CD.TipoDoc = 'NET' And CD.Serie = 'SITE'
        and A1.CDU_DisponivelOnline = 1
    Group By A1.Artigo, A1.Descricao

    Union

    select
        A1.Artigo as Artigo, A1.Descricao as Descricao, FLOOR(MIN(CASE WHEN A2.familia = 39 THEN AA.StkActual / 2 ELSE AA.StkActual END)) as Stock, 0 as QuantidadeEmEncomendasPendentes, 'Componente Stock' As Id
    from
        Artigo A1
        join ComponentesArtigos CA On CA.ArtigoComposto = A1.Artigo
        join Artigo A2 On A2.Artigo = CA.Componente And (A2.Familia IN (37,38,39,45))
        join ArtigoArmazem AA ON AA.Artigo = A2.Artigo And (AA.Armazem IN ('A7'))
        and A1.CDU_DisponivelOnline = 1
    group by
        A1.Artigo,
        A1.Descricao

    Union

    select
        A1.Artigo as Artigo, A1.Descricao as Descricao, (coalesce(MIN(
            CASE
                when AA.Armazem = 'A7' then 
                    CASE WHEN A2.Familia = 98 THEN AA.StkActual END
                else
                    null
            end
        ), MIN(
            CASE
                when AA.Armazem = 'A8' then 
                    CASE WHEN A2.Familia = 98 THEN AA.StkActual END
                else
                    null
            end
        ), MIN(
            CASE
                when AA.Armazem = 'A9' then 
                    CASE WHEN A2.Familia = 98 THEN AA.StkActual END
                else
                    null
            end
        ))) as Stock, 0 as QuantidadeEmEncomendasPendentes, 'Componente Stock' As Id
    from
        Artigo A1
        join ComponentesArtigos CA On CA.ArtigoComposto = A1.Artigo
        join Artigo A2 On A2.Artigo = CA.Componente And (A2.Familia IN (98))
        join ArtigoArmazem AA ON AA.Artigo = A2.Artigo And (AA.Armazem IN ('A7', 'A8', 'A9'))
        and A1.CDU_DisponivelOnline = 1
    group by
        A1.Artigo,
        A1.Descricao

    Union

    Select A1.Artigo as Artigo, A1.Descricao as Descricao, 0 as Stock, Sum(LD.Quantidade - LDS.QuantTrans) as QuantidadeEmEncomendasPendentes, 'Componente Encomenda' As Id
    From Artigo A1
        Inner Join ComponentesArtigos CA On CA.ArtigoComposto = A1.Artigo
        Inner Join Artigo A2 On A2.Artigo = CA.Componente And (A2.Familia IN (37,38,39,45,98))
        Inner Join LinhasDoc LD On LD.Artigo = A2.Artigo And Year(LD.Data) >= 2018 And LD.Quantidade Is Not Null And LD.Quantidade > 0
        Inner Join LinhasDocStatus LDS On LDS.IdLinhasDoc = LD.Id  And LDS.Fechado = 0 And LDS.Quantidade > LDS.QuantTrans
        Inner Join CabecDocStatus CDS On CDS.IdCabecDoc = LD.IdCabecDoc And CDS.Fechado = 0 And CDS.Estado = 'P' And LDS.IdLinhasDoc Is Not Null 
        Inner Join CabecDoc CD On CD.Id = LD.IdCabecDoc And CD.TipoDoc = 'NET' And CD.Serie = 'SITE' And CDS.IdCabecDoc Is Not Null
        and A1.CDU_DisponivelOnline = 1
    Group By A1.Artigo, A1.Descricao

) As Result
Group by Artigo, Descricao
order by Descricao

And I need to get the minimum value of the 3rd and 4th subqueries instead of adding both of them to the SUM(Stock) of the main query. How can I achieve that? I need to maintain the query structure.. I need the MIN from the Result of those queries (3rd and 4th):

select
        A1.Artigo as Artigo, A1.Descricao as Descricao, FLOOR(MIN(CASE WHEN A2.familia = 39 THEN AA.StkActual / 2 ELSE AA.StkActual END)) as Stock, 0 as QuantidadeEmEncomendasPendentes, 'Componente Stock' As Id
    from
        Artigo A1
        join ComponentesArtigos CA On CA.ArtigoComposto = A1.Artigo
        join Artigo A2 On A2.Artigo = CA.Componente And (A2.Familia IN (37,38,39,45))
        join ArtigoArmazem AA ON AA.Artigo = A2.Artigo And (AA.Armazem IN ('A7'))
        and A1.CDU_DisponivelOnline = 1
    group by
        A1.Artigo,
        A1.Descricao

    Union

    select
        A1.Artigo as Artigo, A1.Descricao as Descricao, (coalesce(MIN(
            CASE
                when AA.Armazem = 'A7' then 
                    CASE WHEN A2.Familia = 98 THEN AA.StkActual END
                else
                    null
            end
        ), MIN(
            CASE
                when AA.Armazem = 'A8' then 
                    CASE WHEN A2.Familia = 98 THEN AA.StkActual END
                else
                    null
            end
        ), MIN(
            CASE
                when AA.Armazem = 'A9' then 
                    CASE WHEN A2.Familia = 98 THEN AA.StkActual END
                else
                    null
            end
        ))) as Stock, 0 as QuantidadeEmEncomendasPendentes, 'Componente Stock' As Id
    from
        Artigo A1
        join ComponentesArtigos CA On CA.ArtigoComposto = A1.Artigo
        join Artigo A2 On A2.Artigo = CA.Componente And (A2.Familia IN (98))
        join ArtigoArmazem AA ON AA.Artigo = A2.Artigo And (AA.Armazem IN ('A7', 'A8', 'A9'))
        and A1.CDU_DisponivelOnline = 1
    group by
        A1.Artigo,
        A1.Descricao

Answer

Consider turning subqueries into multiple CTEs, then in UNION query add columns for Stock3 and Stock4 based on corresponding Stock to aggregate for MIN values, filling in NULLs for other queries:

WITH first_agg AS (
    SELECT A1.Artigo AS Artigo, A1.Descricao AS Descricao, 
          SUM(AA.StkActual) AS Stock, 
          0 AS QuantidadeEmEncomendasPendentes, 
          'Artigo Stock' AS Id
    FROM Artigo A1
    INNER JOIN ArtigoArmazem AA 
        ON AA.Artigo = A1.Artigo 
        AND (AA.Armazem IN ('A1','A5','A7','A8','A9'))
        AND A1.CDU_DisponivelOnline = 1
    GROUP BY A1.Artigo, A1.Descricao
), 

second_agg AS (
    SELECT A1.Artigo AS Artigo, A1.Descricao AS Descricao, 
           0 AS Stock, 
           SUM(LD.Quantidade - LDS.QuantTrans) AS QuantidadeEmEncomendasPendentes, 
           'Artigo Encomenda' AS Id
    FROM Artigo A1
        INNER JOIN LinhasDoc LD 
             ON LD.Artigo = A1.Artigo AND Year(LD.Data) >= 2018 
             AND LD.Quantidade Is Not Null AND LD.Quantidade > 0
        INNER JOIN LinhasDocStatus LDS 
             ON LDS.IdLinhasDoc = LD.Id  
             AND LDS.Fechado = 0 AND LDS.Quantidade > LDS.QuantTrans
        INNER JOIN CabecDocStatus CDS 
             ON CDS.IdCabecDoc = LD.IdCabecDoc 
             AND CDS.Fechado = 0 AND CDS.Estado = 'P'
        INNER JOIN CabecDoc CD 
             ON CD.Id = LD.IdCabecDoc 
             AND CD.TipoDoc = 'NET' AND CD.Serie = 'SITE'
        AND A1.CDU_DisponivelOnline = 1
    GROUP BY A1.Artigo, A1.Descricao
),

third_agg AS (    
    SELECT
        A1.Artigo AS Artigo, A1.Descricao AS Descricao, 
        FLOOR(MIN(CASE 
                       WHEN A2.familia = 39 
                       THEN AA.StkActual / 2 
                       ELSE AA.StkActual 
                  END)) AS Stock, 
        0 AS QuantidadeEmEncomendasPendentes, 
        'Componente Stock' AS Id
    FROM
        Artigo A1
        INNER JOIN ComponentesArtigos CA 
              ON CA.ArtigoComposto = A1.Artigo
        INNER JOIN Artigo A2 
              ON A2.Artigo = CA.Componente AND (A2.Familia IN (37,38,39,45))
        INNER JOIN ArtigoArmazem AA 
              ON AA.Artigo = A2.Artigo AND (AA.Armazem IN ('A7'))
        AND A1.CDU_DisponivelOnline = 1
    GROUP BY
        A1.Artigo,
        A1.Descricao
),

fourth_agg AS (
    SELECT A1.Artigo AS Artigo, A1.Descricao AS Descricao, 
            (COALESCE(MIN(
                CASE
                    WHEN AA.Armazem = 'A7' 
                    THEN CASE WHEN A2.Familia = 98 THEN AA.StkActual END
                    ELSE NULL
                END
            ), MIN(
                CASE
                    WHEN AA.Armazem = 'A8' 
                    THEN CASE WHEN A2.Familia = 98 THEN AA.StkActual END
                    ELSE NULL
                END
            ), MIN(
                CASE
                    WHEN AA.Armazem = 'A9' 
                    THEN CASE WHEN A2.Familia = 98 THEN AA.StkActual END
                    ELSE NULL
                END
            ))) AS Stock, 
            0 AS QuantidadeEmEncomendasPendentes, 
            'Componente Stock' AS Id
    FROM
        Artigo A1
        INNER JOIN ComponentesArtigos CA 
              ON CA.ArtigoComposto = A1.Artigo
        INNER JOIN Artigo A2 
              ON A2.Artigo = CA.Componente AND (A2.Familia IN (98))
        INNER JOIN ArtigoArmazem AA 
              ON AA.Artigo = A2.Artigo AND (AA.Armazem IN ('A7', 'A8', 'A9'))
              AND A1.CDU_DisponivelOnline = 1
    GROUP BY
        A1.Artigo,
        A1.Descricao
),

fifth_agg AS (
    SELECT A1.Artigo AS Artigo, A1.Descricao AS Descricao, 
           0 AS Stock, 
           SUM(LD.Quantidade - LDS.QuantTrans) AS QuantidadeEmEncomendasPendentes, 
           'Componente Encomenda' AS Id
    FROM Artigo A1
        INNER JOIN ComponentesArtigos CA 
              ON CA.ArtigoComposto = A1.Artigo
        INNER JOIN Artigo A2 
              ON A2.Artigo = CA.Componente AND (A2.Familia IN (37,38,39,45,98))
        INNER JOIN LinhasDoc LD 
              ON LD.Artigo = A2.Artigo AND Year(LD.Data) >= 2018 
              AND LD.Quantidade Is Not Null AND LD.Quantidade > 0
        INNER JOIN LinhasDocStatus LDS 
              ON LDS.IdLinhasDoc = LD.Id  
              AND LDS.Fechado = 0 AND LDS.Quantidade > LDS.QuantTrans
        INNER JOIN CabecDocStatus CDS 
              ON CDS.IdCabecDoc = LD.IdCabecDoc AND CDS.Fechado = 0 
              AND CDS.Estado = 'P' AND LDS.IdLinhasDoc Is Not Null 
        INNER JOIN CabecDoc CD 
              ON CD.Id = LD.IdCabecDoc AND CD.TipoDoc = 'NET' 
              AND CD.Serie = 'SITE' AND CDS.IdCabecDoc Is Not Null
        AND A1.CDU_DisponivelOnline = 1
    GROUP BY A1.Artigo, A1.Descricao
)


SELECT Artigo, Descricao, 
       SUM(Stock) AS Stock, 
       SUM(QuantidadeEmEncomendasPendentes) AS QuantidadeEmEncomendasPendentes, 
       SUM(Stock) - SUM(QuantidadeEmEncomendasPendentes) AS StockTotal,
       MIN(Stock3) AS MIN_Stock3,
       MIN(Stock4) AS Min_Stock4

FROM (    
       SELECT Artigo, Descricao, Stock, NULL AS Stock3, NULL AS Stock4, 
              QuantidadeEmEncomendasPendentes, ID 
       FROM first_agg

       UNION 

       SELECT Artigo, Descricao, Stock, NULL AS Stock3, NULL AS Stock4, 
              QuantidadeEmEncomendasPendentes, ID 
       FROM second_agg

       UNION

       SELECT Artigo, Descricao, Stock, Stock AS Stock3, NULL AS Stock4, 
              QuantidadeEmEncomendasPendentes, ID 
       FROM third_agg

       UNION

       SELECT Artigo, Descricao, Stock, NULL AS Stock3, Stock AS Stock4, 
              QuantidadeEmEncomendasPendentes, ID 
       FROM fourth_agg

       UNION

       SELECT Artigo, Descricao, Stock, NULL AS Stock3, NULL AS Stock4, 
              QuantidadeEmEncomendasPendentes, ID 
       FROM fifth_agg      

) AS Result
GROUP BY Artigo, Descricao
ORDER BY Descricao