Error - Each GROUP BY expression must contain at least one column

Question

Error - Each GROUP BY expression must contain at least one column

jagjitsingh

Ten Centuries

Points: 1059
More actions
December 18, 2021 at 8:47 am

#3965046

Hi
Error - Each GROUP BY expression must contain at least one column that is not an outer reference.
SELECT
T1.U_VendCode,T1.U_CabNo,T1.U_VendName as "Vendor Name",Count(*),
(Select Count(*) from [tbl2] where T1.U_APDE <> 'N'
group by T1.U_VendCode,T1.U_CabNo,T1.U_VendName,T1.U_Custcode,T1.U_Custname,T1.U_APDE,T1.U_ARDE,T1.U_ItemCode),
T1.U_CustCode as "Cust Code",T1.U_CustName as "Cust Name",T1.U_ItemCode
FROM [tbl1] T0
inner join [tbl2] T1 on T0.Docentry = T1.DocEntry
where (T1.U_ItemCode is not null) and (T0.U_DATE BETWEEN '2021-03-01' and '2021-03-31' )
and T1.U_CustCode = 'A01'
group by T1.U_VendCode,T1.U_CabNo,T1.U_VendName,T1.U_Custcode,T1.U_Custname,T1.U_APDE,T1.U_ARDE,T1.U_ItemCode
Thanks

February 21, 2022 at 6:43 am

This was removed by the editor as SPAM

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

The error is clear - you need at least 1 column that is not from the outer reference - that is, at least one column from [tbl1] in the group by.

For someone who has over 1000 points, you should be able to use the insert/edit code sample button. Please use that when you post code - and I highly recommend you learn how to format your SQL code to be more readable. Putting everything on a single line just makes it harder to read and therefore harder to figure out.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

jagjitsingh Ten Centuries Points: 1059 More actions · Answer 2

hi

I want same group by condition which is in outer. What is the other to solve this issue

Thanks

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

You can remove tbl1 from the query and use some other check to exclude the rows - for example, use IN or EXISTS. You could add one or more columns from tbl1 in the group by, or you can reconsider the actual results you are looking for...

Also - there is no reason to use a sub-query to get a count from a table you are already querying. Instead of a sub-query for that, use a SUM with a CASE expression: SUM(CASE WHEN T1.U_APDE <> 'N' THEN 1 ELSE 0 END).

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Sergiy SSC Guru Points: 110208 More actions · Answer 4

You don't use any field from T0 in the SELECT part, so you better put that table to WHERE EXISTS clause.

And with Jeffrey's suggestion about the subquery, you should get something like this:

SELECT 
T1.U_VendCode,T1.U_CabNo,T1.U_VendName as "Vendor Name", 
Count(*), 
Count(CASE WHEN T1.U_APDE <> 'N' then 1 else NULL END), 
T1.U_CustCode as "Cust Code",T1.U_CustName as "Cust Name",T1.U_ItemCode
FROM [tbl2] T1
WHERE T1.U_ItemCode is not null
AND EXISTS (select * from [tbl1] T0
WHERE T0.Docentry = T1.DocEntry
AND T0.U_DATE BETWEEN '2021-03-01' and '2021-03-31' 
)
group by T1.U_VendCode,T1.U_CabNo,T1.U_VendName,
T1.U_Custcode,T1.U_Custname,T1.U_APDE,T1.U_ARDE,T1.U_ItemCode

_____________
Code for TallyGenerator