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

huangapple go评论58阅读模式
英文:

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.

huangapple
  • 本文由 发表于 2023年5月25日 21:56:11
  • 转载请务必保留本文链接:https://go.coder-hub.com/76333087.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定