英文:
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
问题
我一直在尝试处理这个问题,但似乎无法清除错误。我在查询中使用了"group by"子句,所以我不确定我哪里出错了。错误出现在我第一个左连接的地方,我尝试对重量进行求和。
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%';
希望这能帮助你解决问题。
英文:
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%'
答案1
得分: 0
尝试将您的子查询更改为以下内容:
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
不确定这是否会给您想要的相同结果,但至少应该可以工作。
英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论