SUM values of an INNER JOIN and many LEFT JOINed tables

Question

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;

Answer 1

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