How to fix column invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause issue

Question

I have been playing around with this for quite some bit and cant seem to clear the error. I have the group by clause in my query so Im not sure where im going wrong. The error is coming from my first left outer join where I try to sum the weight.

select 
l.me_id,
l.CREATED_DATE,
st.location_name,
st.location_address_1,
st.location_city,
st.location_state_code,
st.location_postal_code,
l.mode_type,
li.weight,
li.hazmat,
li.PRODUCT_DESCRIPTION


from tp.table l



LEFT OUTER JOIN
(Select m.* from
(SELECT truck_ID,hazmat,sum(weight) as 'Weight', product_description,
Row_number() OVER(PARTITION BY ME_ID ORDER BY freight_ID DESC) AS R_NO
FROM [TP].[table2] where status='active'
GROUP BY ME_ID) m where R_NO=1) li
ON L.truck_ID=li.truck_id

LEFT OUTER JOIN
(Select n.* from
(SELECT ME_ID, location_name, location_address_1, location_city, location_state_code,location_postal_code, 
Row_number() OVER(PARTITION BY ME_ID ORDER BY stop_ID DESC) AS R_NO
FROM [TP].[table3] where status='active' and STOP_SEQUENCE_NUMBER = '1') n where R_NO=1) st
ON L.truck_ID=st.truck_id



where year(l.CREATED_date) ='2022' 

and LOCATION_address_1 LIKE '%6210 GLENWAY%'
or LOCATION_address_1  like '%480 WILEY%'
or LOCATION_address_1  like '%575 5TH%' -- not present in data  
or LOCATION_address_1 like '%100 E ROOSEVELT%'
or LOCATION_address_1 like '%565 5TH%' -- not present in data  
or LOCATION_address_1  like '%1001 SE TV%'
or LOCATION_address_1  like '%435 HUDSON%'
or LOCATION_address_1  like '%575 FIFTH%'-- not present in data  
or LOCATION_address_1  like '%3940 PLANK%'
or LOCATION_address_1  like '%609 SW 8TH%'
or LOCATION_address_1  like '%28145 HARRISON%' 
or LOCATION_address_1  like '%2620 SW 17Th%'
or LOCATION_address_1  like '%833 W 16TH%'
or LOCATION_address_1  like '%992 POQUONNOCK%'
or LOCATION_address_1  like '%4555 DANVERS%'
or LOCATION_address_1  like '%100 COMMERCE DR%'
or LOCATION_address_1  like '%6600 JEFFERSON%'

Answer 1

Try changing your sub-select to the following:

  SELECT ME_ID
        ,hazmat
        ,sum(weight) as 'Weight'
        ,product_description
        ,Row_number() OVER(PARTITION BY ME_ID ORDER BY freight_ID DESC) AS R_NO
    FROM [TP].[line_item]
   WHERE status='active'
GROUP BY ME_ID
        ,hazmat
        ,product_description
        ,freight_ID

Not sure if that will give you the same results you want, but it should at least work.