SUM values of an INNER JOIN and many LEFT JOINed tables-CodePudding

I have a complex query with one INNER JOINed table and many LEFT JOINed tables to a main table. One of the tables has more than one rows that correspond to each row of the main table, and I want to include a SUM() of a specific field of those rows in my SELECT query.

Of course SQL server started notifying me about one by one field being

not contained in either an aggregate function or the GROUP BY clause

, so I started adding them in a GROUP BY clause, until I ran into a text field... In that case, I got the error that

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

So, in that case, how can I overcome that obstacle?

Also, down the road there are even more complex cases of SELECTed fields, so how am I supposed to overcome this situation?

Below I include the whole query, and if requested, I can provide an oversimplified DB fiddle, at least between the main table and this LEFT JOINed one-to-many table...

SELECT sub.SUBSTITUTECODE AS SKU,
       prod.CODE,
       prod.DESCRIPTION AS TITLE,
       prod.REMARKS AS DESCRIPTION, -- <---- This is the text field...
       prod.DESCR2 AS SHORTDESCRIPTION,
       manuf.DESCR AS MANUFACTURER,
       CONCAT(UPPER(LTRIM(RTRIM(cat1.DESCR))), '>', UPPER(LTRIM(RTRIM(cat2.DESCR))), '>', UPPER(LTRIM(RTRIM(cat3.DESCR)))) AS CATEGORIES,
       CONVERT(DECIMAL(10,2), CONVERT(DECIMAL(10,3), CASE WHEN ISNULL(prod.FWHSPRICE, 0) = 0 THEN ISNULL(prod.RTLPRICE, 0) ELSE ISNULL(prod.FWHSPRICE, 0) END)) AS REGULARPRICE,
       CONVERT(DECIMAL(10,2), CONVERT(DECIMAL(10,3), ISNULL(prod.FLDFLOAT3, 0))) AS SALEPRICE,
       CHOOSE(sub.SIZEPOS, szlist.SIZE1, szlist.SIZE2, szlist.SIZE3, szlist.SIZE4, szlist.SIZE5, szlist.SIZE6, szlist.SIZE7, szlist.SIZE8, szlist.SIZE9, szlist.SIZE10, szlist.SIZE11, szlist.SIZE12, szlist.SIZE13, szlist.SIZE14, szlist.SIZE15, szlist.SIZE16, szlist.SIZE17, szlist.SIZE18, szlist.SIZE19, szlist.SIZE20, szlist.SIZE21, szlist.SIZE22, szlist.SIZE23, szlist.SIZE24, szlist.SIZE25) AS SIZE,
       CHOOSE(sub.SIZEPOS, SUM(CASE WHEN ISNULL(qtys.SIZE1, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE1, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE2, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE2, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE3, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE3, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE4, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE4, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE5, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE5, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE6, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE6, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE7, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE7, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE8, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE8, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE9, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE9, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE10, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE10, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE11, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE11, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE12, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE12, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE13, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE13, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE14, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE14, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE15, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE15, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE16, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE16, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE17, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE17, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE18, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE18, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE19, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE19, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE20, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE20, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE21, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE21, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE22, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE22, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE23, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE23, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE24, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE24, 0) END), SUM(CASE WHEN ISNULL(qtys.SIZE25, 0) < 0 THEN 0 ELSE ISNULL(qtys.SIZE25, 0) END)) AS QUANTITIES,
       col.COLORDESCR AS COLOR,
       prod.FLTID3 AS SIZEGUIDE,
       ISNULL(prod.FLDSTRING1, 0) AS SLIDERNEWPRODUCT,
       ISNULL(prod.FLDFLOAT5, 0) AS SLIDERCUSTOM1,
       ISNULL(prod.FLDFLOAT6, 0) AS SLIDERCUSTOM2,
       prod.zWebLISTSEO AS SEOTITLE,
       prod.zWEBDESCRSEO AS SEODESCRIPTION,
       prod.FLDSTRING2 AS SKROUTZWEIGHT
FROM SUBSTITUTE AS sub
INNER JOIN MATERIAL AS prod ON prod.ID = sub.ITEID AND prod.FLTID1 = 1 AND prod.COMID = 12 AND prod.CODE NOT IN ('FX8707 ', '749747-010 ', '7111653 ', 'ZY9021-12W ', 'ZY9021-12R ', '033123-08B ', '30VERMDBLU ', '053003-10 ', 'M20-73354-10 ', '935088-05 ', '935088-02 ', '216603-RS053 ', '194342-01 ', '1E03550 ', '000024555 ', '071010-01 ', '071933-01 ', 'W9-66333-10 ', 'W9-66333-26 ', 'W9-66338-10 ', '573571-01 ', 'H087Y ', 'FV3407 ', 'D012L ', '358038-04 ', '358038-02 ', '114449-KK001 ', 'BV3636-010 ', 'RC5051PT-28633W ', '106312-06 ', 'DC4054-068 ', '370488-11 ', 'H68096 ', 'AT1801-406 ', 'AT1801-600 ', 'CD3199-455 ', '000925701 ', 'G71616 ', 'CZ8646 ', '42051-BLUE ', '42051-ORANGE ', '42051-YELLOW ', '42054-BLUE ', 'BA5901-610 ', 'F9505-300')
LEFT JOIN ITECOLOR AS col ON col.COLORCODE = sub.COLORCODE AND col.ITEID = sub.ITEID
LEFT JOIN MANUFACTURER AS manuf ON manuf.CODEID = prod.MNFID
LEFT JOIN ITEMCATEGORY AS cat1 ON cat1.CODEID = prod.ICTID AND cat1.COMID = 12
LEFT JOIN ITEMGROUP2 AS cat2 ON cat2.CODEID = prod.IGSID AND cat2.COMID = 12
LEFT JOIN ITEMGROUP AS cat3 ON cat3.CODEID = prod.IGPID AND cat3.COMID = 12
LEFT JOIN SIZELIST AS szlist ON szlist.CODEID = prod.MAINSZLID AND szlist.COMID = 12
LEFT JOIN COLORSIZEQTYS AS qtys ON qtys.ITEID = sub.ITEID AND qtys.COLORCODE = sub.COLORCODE AND qtys.QTYMODE = 1 -- <---- This is the one-to-many table
GROUP BY sub.SUBSTITUTECODE, prod.CODE, prod.DESCRIPTION, prod.REMARKS;

CodePudding user response：

You can remove the grouping from your query and use outer apply

I'm not going to reproduce your entire query but the gist would be along the lines of

select...
Choose(sub.SIZEPOS, qtys.s1, qtys.s2...) as Quantities
from SUBSTITUTE as sub
...
outer apply (
    SUM(case when IsNull(qtys.SIZE1, 0) < 0 then 0 else IsNull(qtys.SIZE1, 0) end) s1,
    SUM(case when IsNull(qtys.SIZE2, 0) < 0 then 0 else IsNull(qtys.SIZE2, 0) end) s2...
    from COLORSIZEQTYS qtys 
    where qtys.ITEID = sub.ITEID and qtys.COLORCODE = sub.COLORCODE and qtys.QTYMODE = 1
)qtys

CodePudding user response：

And this is the final query. I'm posting it just for the sake of completeness. Thank you @stu once again!

SELECT sub.SUBSTITUTECODE AS SKU,
    prod.CODE,
    prod.DESCRIPTION AS TITLE,
    prod.REMARKS AS DESCRIPTION,
    prod.DESCR2 AS SHORTDESCRIPTION,
    manuf.DESCR AS MANUFACTURER,
    CONCAT(UPPER(LTRIM(RTRIM(cat1.DESCR))), '>', UPPER(LTRIM(RTRIM(cat2.DESCR))), '>', UPPER(LTRIM(RTRIM(cat3.DESCR)))) AS CATEGORIES,
    CONVERT(DECIMAL(10,2), CONVERT(DECIMAL(10,3), CASE WHEN ISNULL(prod.FWHSPRICE, 0) = 0 THEN ISNULL(prod.RTLPRICE, 0) ELSE ISNULL(prod.FWHSPRICE, 0) END)) AS REGULARPRICE,
    CONVERT(DECIMAL(10,2), CONVERT(DECIMAL(10,3), ISNULL(prod.FLDFLOAT3, 0))) AS SALEPRICE,
    CHOOSE(sub.SIZEPOS, szlist.SIZE1, szlist.SIZE2, szlist.SIZE3, szlist.SIZE4, szlist.SIZE5, szlist.SIZE6, szlist.SIZE7, szlist.SIZE8, szlist.SIZE9, szlist.SIZE10, szlist.SIZE11, szlist.SIZE12, szlist.SIZE13, szlist.SIZE14, szlist.SIZE15, szlist.SIZE16, szlist.SIZE17, szlist.SIZE18, szlist.SIZE19, szlist.SIZE20, szlist.SIZE21, szlist.SIZE22, szlist.SIZE23, szlist.SIZE24, szlist.SIZE25) AS SIZE,
    CHOOSE(sub.SIZEPOS, qtys.TOTAL1, qtys.TOTAL2, qtys.TOTAL3, qtys.TOTAL4, qtys.TOTAL5, qtys.TOTAL6, qtys.TOTAL7, qtys.TOTAL8, qtys.TOTAL9, qtys.TOTAL10, qtys.TOTAL11, qtys.TOTAL12, qtys.TOTAL13, qtys.TOTAL14, qtys.TOTAL15, qtys.TOTAL16, qtys.TOTAL17, qtys.TOTAL18, qtys.TOTAL19, qtys.TOTAL20, qtys.TOTAL21, qtys.TOTAL22, qtys.TOTAL23, qtys.TOTAL24, qtys.TOTAL25) AS QUANTITY,
    col.COLORDESCR AS COLOR,
    prod.FLTID3 AS SIZEGUIDE,
    ISNULL(prod.FLDSTRING1, 0) AS SLIDERNEWPRODUCT,
    ISNULL(prod.FLDFLOAT5, 0) AS SLIDERCUSTOM1,
    ISNULL(prod.FLDFLOAT6, 0) AS SLIDERCUSTOM2,
    prod.zWebLISTSEO AS SEOTITLE,
    prod.zWEBDESCRSEO AS SEODESCRIPTION,
    prod.FLDSTRING2 AS SKROUTZWEIGHT
FROM SUBSTITUTE AS sub
    INNER JOIN MATERIAL AS prod ON prod.ID = sub.ITEID AND prod.FLTID1 = 1 AND prod.COMID = 12 AND prod.CODE NOT IN ('FX8707 ', '749747-010 ', '7111653 ', 'ZY9021-12W ', 'ZY9021-12R ', '033123-08B ', '30VERMDBLU ', '053003-10 ', 'M20-73354-10 ', '935088-05 ', '935088-02 ', '216603-RS053 ', '194342-01 ', '1E03550 ', '000024555 ', '071010-01 ', '071933-01 ', 'W9-66333-10 ', 'W9-66333-26 ', 'W9-66338-10 ', '573571-01 ', 'H087Y ', 'FV3407 ', 'D012L ', '358038-04 ', '358038-02 ', '114449-KK001 ', 'BV3636-010 ', 'RC5051PT-28633W ', '106312-06 ', 'DC4054-068 ', '370488-11 ', 'H68096 ', 'AT1801-406 ', 'AT1801-600 ', 'CD3199-455 ', '000925701 ', 'G71616 ', 'CZ8646 ', '42051-BLUE ', '42051-ORANGE ', '42051-YELLOW ', '42054-BLUE ', 'BA5901-610 ', 'F9505-300')
    LEFT JOIN ITECOLOR AS col ON col.COLORCODE = sub.COLORCODE AND col.ITEID = sub.ITEID
    LEFT JOIN MANUFACTURER AS manuf ON manuf.CODEID = prod.MNFID
    LEFT JOIN ITEMCATEGORY AS cat1 ON cat1.CODEID = prod.ICTID AND cat1.COMID = 12
    LEFT JOIN ITEMGROUP2 AS cat2 ON cat2.CODEID = prod.IGSID AND cat2.COMID = 12
    LEFT JOIN ITEMGROUP AS cat3 ON cat3.CODEID = prod.IGPID AND cat3.COMID = 12
    LEFT JOIN SIZELIST AS szlist ON szlist.CODEID = prod.MAINSZLID AND szlist.COMID = 12
OUTER APPLY (
SELECT CASE WHEN (ISNULL(stk.QTY1, 0) - ISNULL(rsv.QTY1, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY1, 0) - ISNULL(rsv.QTY1, 0)) END AS TOTAL1, CASE WHEN (ISNULL(stk.QTY2, 0) - ISNULL(rsv.QTY2, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY2, 0) - ISNULL(rsv.QTY2, 0)) END AS TOTAL2, CASE WHEN (ISNULL(stk.QTY3, 0) - ISNULL(rsv.QTY3, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY3, 0) - ISNULL(rsv.QTY3, 0)) END AS TOTAL3, CASE WHEN (ISNULL(stk.QTY4, 0) - ISNULL(rsv.QTY4, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY4, 0) - ISNULL(rsv.QTY4, 0)) END AS TOTAL4, CASE WHEN (ISNULL(stk.QTY5, 0) - ISNULL(rsv.QTY5, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY5, 0) - ISNULL(rsv.QTY5, 0)) END AS TOTAL5, CASE WHEN (ISNULL(stk.QTY6, 0) - ISNULL(rsv.QTY6, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY6, 0) - ISNULL(rsv.QTY6, 0)) END AS TOTAL6, CASE WHEN (ISNULL(stk.QTY7, 0) - ISNULL(rsv.QTY7, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY7, 0) - ISNULL(rsv.QTY7, 0)) END AS TOTAL7, CASE WHEN (ISNULL(stk.QTY8, 0) - ISNULL(rsv.QTY8, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY8, 0) - ISNULL(rsv.QTY8, 0)) END AS TOTAL8, CASE WHEN (ISNULL(stk.QTY9, 0) - ISNULL(rsv.QTY9, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY9, 0) - ISNULL(rsv.QTY9, 0)) END AS TOTAL9, CASE WHEN (ISNULL(stk.QTY10, 0) - ISNULL(rsv.QTY10, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY10, 0) - ISNULL(rsv.QTY10, 0)) END AS TOTAL10, CASE WHEN (ISNULL(stk.QTY11, 0) - ISNULL(rsv.QTY11, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY11, 0) - ISNULL(rsv.QTY11, 0)) END AS TOTAL11, CASE WHEN (ISNULL(stk.QTY12, 0) - ISNULL(rsv.QTY12, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY12, 0) - ISNULL(rsv.QTY12, 0)) END AS TOTAL12, CASE WHEN (ISNULL(stk.QTY13, 0) - ISNULL(rsv.QTY13, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY13, 0) - ISNULL(rsv.QTY13, 0)) END AS TOTAL13, CASE WHEN (ISNULL(stk.QTY14, 0) - ISNULL(rsv.QTY14, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY14, 0) - ISNULL(rsv.QTY14, 0)) END AS TOTAL14, CASE WHEN (ISNULL(stk.QTY15, 0) - ISNULL(rsv.QTY15, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY15, 0) - ISNULL(rsv.QTY15, 0)) END AS TOTAL15, CASE WHEN (ISNULL(stk.QTY16, 0) - ISNULL(rsv.QTY16, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY16, 0) - ISNULL(rsv.QTY16, 0)) END AS TOTAL16, CASE WHEN (ISNULL(stk.QTY17, 0) - ISNULL(rsv.QTY17, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY17, 0) - ISNULL(rsv.QTY17, 0)) END AS TOTAL17, CASE WHEN (ISNULL(stk.QTY18, 0) - ISNULL(rsv.QTY18, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY18, 0) - ISNULL(rsv.QTY18, 0)) END AS TOTAL18, CASE WHEN (ISNULL(stk.QTY19, 0) - ISNULL(rsv.QTY19, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY19, 0) - ISNULL(rsv.QTY19, 0)) END AS TOTAL19, CASE WHEN (ISNULL(stk.QTY20, 0) - ISNULL(rsv.QTY20, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY20, 0) - ISNULL(rsv.QTY20, 0)) END AS TOTAL20, CASE WHEN (ISNULL(stk.QTY21, 0) - ISNULL(rsv.QTY21, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY21, 0) - ISNULL(rsv.QTY21, 0)) END AS TOTAL21, CASE WHEN (ISNULL(stk.QTY22, 0) - ISNULL(rsv.QTY22, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY22, 0) - ISNULL(rsv.QTY22, 0)) END AS TOTAL22, CASE WHEN (ISNULL(stk.QTY23, 0) - ISNULL(rsv.QTY23, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY23, 0) - ISNULL(rsv.QTY23, 0)) END AS TOTAL23, CASE WHEN (ISNULL(stk.QTY24, 0) - ISNULL(rsv.QTY24, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY24, 0) - ISNULL(rsv.QTY24, 0)) END AS TOTAL24, CASE WHEN (ISNULL(stk.QTY25, 0) - ISNULL(rsv.QTY25, 0)) < 0 THEN 0 ELSE (ISNULL(stk.QTY25, 0) - ISNULL(rsv.QTY25, 0)) END AS TOTAL25
    FROM (SELECT SUM(CASE WHEN ISNULL(SIZE1, 0) < 0 THEN 0 ELSE ISNULL(SIZE1, 0) END) AS QTY1, SUM(CASE WHEN ISNULL(SIZE2, 0) < 0 THEN 0 ELSE ISNULL(SIZE2, 0) END) AS QTY2, SUM(CASE WHEN ISNULL(SIZE3, 0) < 0 THEN 0 ELSE ISNULL(SIZE3, 0) END) AS QTY3, SUM(CASE WHEN ISNULL(SIZE4, 0) < 0 THEN 0 ELSE ISNULL(SIZE4, 0) END) AS QTY4, SUM(CASE WHEN ISNULL(SIZE5, 0) < 0 THEN 0 ELSE ISNULL(SIZE5, 0) END) AS QTY5, SUM(CASE WHEN ISNULL(SIZE6, 0) < 0 THEN 0 ELSE ISNULL(SIZE6, 0) END) AS QTY6, SUM(CASE WHEN ISNULL(SIZE7, 0) < 0 THEN 0 ELSE ISNULL(SIZE7, 0) END) AS QTY7, SUM(CASE WHEN ISNULL(SIZE8, 0) < 0 THEN 0 ELSE ISNULL(SIZE8, 0) END) AS QTY8, SUM(CASE WHEN ISNULL(SIZE9, 0) < 0 THEN 0 ELSE ISNULL(SIZE9, 0) END) AS QTY9, SUM(CASE WHEN ISNULL(SIZE10, 0) < 0 THEN 0 ELSE ISNULL(SIZE10, 0) END) AS QTY10, SUM(CASE WHEN ISNULL(SIZE11, 0) < 0 THEN 0 ELSE ISNULL(SIZE11, 0) END) AS QTY11, SUM(CASE WHEN ISNULL(SIZE12, 0) < 0 THEN 0 ELSE ISNULL(SIZE12, 0) END) AS QTY12, SUM(CASE WHEN ISNULL(SIZE13, 0) < 0 THEN 0 ELSE ISNULL(SIZE13, 0) END) AS QTY13, SUM(CASE WHEN ISNULL(SIZE14, 0) < 0 THEN 0 ELSE ISNULL(SIZE14, 0) END) AS QTY14, SUM(CASE WHEN ISNULL(SIZE15, 0) < 0 THEN 0 ELSE ISNULL(SIZE15, 0) END) AS QTY15, SUM(CASE WHEN ISNULL(SIZE16, 0) < 0 THEN 0 ELSE ISNULL(SIZE16, 0) END) AS QTY16, SUM(CASE WHEN ISNULL(SIZE17, 0) < 0 THEN 0 ELSE ISNULL(SIZE17, 0) END) AS QTY17, SUM(CASE WHEN ISNULL(SIZE18, 0) < 0 THEN 0 ELSE ISNULL(SIZE18, 0) END) AS QTY18, SUM(CASE WHEN ISNULL(SIZE19, 0) < 0 THEN 0 ELSE ISNULL(SIZE19, 0) END) AS QTY19, SUM(CASE WHEN ISNULL(SIZE20, 0) < 0 THEN 0 ELSE ISNULL(SIZE20, 0) END) AS QTY20, SUM(CASE WHEN ISNULL(SIZE21, 0) < 0 THEN 0 ELSE ISNULL(SIZE21, 0) END) AS QTY21, SUM(CASE WHEN ISNULL(SIZE22, 0) < 0 THEN 0 ELSE ISNULL(SIZE22, 0) END) AS QTY22, SUM(CASE WHEN ISNULL(SIZE23, 0) < 0 THEN 0 ELSE ISNULL(SIZE23, 0) END) AS QTY23, SUM(CASE WHEN ISNULL(SIZE24, 0) < 0 THEN 0 ELSE ISNULL(SIZE24, 0) END) AS QTY24, SUM(CASE WHEN ISNULL(SIZE25, 0) < 0 THEN 0 ELSE ISNULL(SIZE25, 0) END) AS QTY25
        FROM COLORSIZEQTYS
        WHERE ITEID = sub.ITEID
            AND COLORCODE = sub.COLORCODE
            AND QTYMODE = 1
        ) AS stk
        CROSS JOIN (SELECT SUM(ISNULL(SIZE1, 0)) AS QTY1, SUM(ISNULL(SIZE2, 0)) AS QTY2, SUM(ISNULL(SIZE3, 0)) AS QTY3, SUM(ISNULL(SIZE4, 0)) AS QTY4, SUM(ISNULL(SIZE5, 0)) AS QTY5, SUM(ISNULL(SIZE6, 0)) AS QTY6, SUM(ISNULL(SIZE7, 0)) AS QTY7, SUM(ISNULL(SIZE8, 0)) AS QTY8, SUM(ISNULL(SIZE9, 0)) AS QTY9, SUM(ISNULL(SIZE10, 0)) AS QTY10, SUM(ISNULL(SIZE11, 0)) AS QTY11, SUM(ISNULL(SIZE12, 0)) AS QTY12, SUM(ISNULL(SIZE13, 0)) AS QTY13, SUM(ISNULL(SIZE14, 0)) AS QTY14, SUM(ISNULL(SIZE15, 0)) AS QTY15, SUM(ISNULL(SIZE16, 0)) AS QTY16, SUM(ISNULL(SIZE17, 0)) AS QTY17, SUM(ISNULL(SIZE18, 0)) AS QTY18, SUM(ISNULL(SIZE19, 0)) AS QTY19, SUM(ISNULL(SIZE20, 0)) AS QTY20, SUM(ISNULL(SIZE21, 0)) AS QTY21, SUM(ISNULL(SIZE22, 0)) AS QTY22, SUM(ISNULL(SIZE23, 0)) AS QTY23, SUM(ISNULL(SIZE24, 0)) AS QTY24, SUM(ISNULL(SIZE25, 0)) AS QTY25
        FROM STORECOLORSIZEEST
        WHERE ITEID = sub.ITEID
            AND COLORCODE = sub.COLORCODE
        ) AS rsv
) AS qtys;