Getting error "Each GROUP BY expression must contain at least ..." 1

[ghbeers]

I don't understand why I'm getting the error "Each GROUP BY expression must contain at least one column that is not an outer reference" because I really do not understand what it means. Can someone help me understand its true meaning?

I have a temp which is driving the selection. It is correctly populated. But when I run the report to pull data from the table and other related tables I get the above error on the item that I am doing a SUM. When I comment out this item the report runs correctly. I thought I had done other reports that are nearly identical, but this one is giving me problems.

What am I doing wrong that is causing this error? THANK YOU.


DECLARE @AWDP AS VARCHAR(10);
SET @AWDP = '2016/SP';

DECLARE @TATABLE TABLE -- which has been populated correctly
(
TA_KEY VARCHAR(25),
TA_AMT DECIMAL (10),
TA_TX DECIMAL (10),
TA_AMT_DIFF DECIMAL (10),
TA_ACTION VARCHAR(2),
TA_AWD VARCHAR(10),
TA_STU_ID VARCHAR (7)
)

SELECT DISTINCT
P.ID,
P.LAST_NAME,
P.FIRST_NAME,
@AWDP as 'Term',
AWD.AW_TYPE,
AWD.AW_CATEGORY,
AWD.AW_ID,
AWD.AW_DESCRIPTION,
TTAB.TA_AMT,
TTAB.TA_TX,
TTAB.TA_AMT_DIFF,
TTAB.TA_ACTION,

-- getting the error on the following item

(SELECT SUM(STC.STC_CRED)
FROM STUDENT_ACAD_CRED STC1
JOIN STUDENT_TERMS_LS STL1 ON STL1.STUDENT_TERMS_ID = TTAB.TA_STU_ID + '*' + @AWDP + '*UG'
WHERE STC1.STUDENT_ACAD_CRED_ID = STL1.STTR_SCHEDULE
GROUP BY TTAB.TA_STU_ID) REG_CRED,

SR.SAPR_TRM_REG_CRED,
SR.SAPR_CALC_SAP_STATUS,
SR.SAPR_OVR_SAP_STATUS

FROM @TATABLE TTAB

LEFT JOIN PERSON P ON TTAB.TA_STU_ID = P.ID
LEFT JOIN AWARDS AWD ON TTAB.TA_AWD = AWD.AW_ID
LEFT JOIN FIN_AID_LS FINL ON P.ID = FINL.FA_STUDENT_ID
LEFT JOIN SAP_RESULTS SR ON FINL.FA_SAP_RESULTS_ID = SR.SAP_RESULTS_ID
AND SR.SAPR_CALC_THRU_TERM = @AWDP
LEFT JOIN STUDENT_TERMS_LS STTRL2 ON STTRL2.STUDENT_TERMS_ID = P.ID + '*' + @AWDP + '*UG'
AND STTRL2.STTR_SCHEDULE IS NOT NULL
LEFT JOIN STUDENT_ACAD_CRED STC ON STTRL2.STTR_SCHEDULE = STC.STUDENT_ACAD_CRED_ID
WHERE TTAB.TA_AMT > 0
AND AWD.AW_TYPE IN ('F','O','S','O')

 

[dhookom]

Could you try again without underlining since the underscores are getting lost?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[ghbeers]

I just removed the underlining. Thank you for pointing that out.

 

[Olaf Doschke]

GROUP BY TTAB.TA_STU_ID is an outer reference, as it references table TTAB, while your inner subquery is about STUDENT_ACAD_CRED

Bye, Olaf.

 

[ghbeers]

When I changed that item to:

(SELECT SUM(STC.STC_CRED)
FROM STUDENT_ACAD_CRED STC1
JOIN STUDENT_TERMS_LS STL1 ON STL1.STUDENT_TERMS_ID = TTAB.TA_STU_ID + '*' + @AWDP + '*UG'
WHERE STC1.STUDENT_ACAD_CRED_ID = STL1.STTR_SCHEDULE
GROUP BY STC1.STC_PERSON_ID) REG_CRED,

I think I can assume that the above cleared up my first problem, but now I get this familiar error "Column 'PERSON.ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause." Complaining about the P.ID, the first column in my select.

So I changed the P.ID to TTAB.TA_STU_ID from the temp table and I got error "Column '@TATABLE.TA_STU_ID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

So I then added the GROUP BY using the TTAB.TA_STU_ID as the first column along with the rest of the columns in the select, I get the error "Each GROUP BY expression must contain at least one column that is not an outer reference."

I'm just not understanding what's going on. Is it the temp table that is messing me up? I must use the temp table because I've previously loaded it with the information related to a dynamic file suite based on the year, building a dynamic string "select stmt" and executing it and then inserting the data into the table.

I am totally confused because I know I've done similar queries in the past. I can't see what is messing it up.

 

[Olaf Doschke]

What do you get (result or error), when you do the query without the subquery creating the REG_CRED column?
I don't see any aggregations done in your outer query, that would ask for a GROUP BY and cause that error.

Bye, Olaf.

 

[ghbeers]

I get perfect results in the other columns without having to group anything.

 

[Olaf Doschke]

And finally doing this alone:

SELECT SUM(STC.STC_CRED)
FROM STUDENT_ACAD_CRED STC1
JOIN STUDENT_TERMS_LS STL1 ON STL1.STUDENT_TERMS_ID = [highlight #FCE94F]STC1.STC_PERSON_ID[/highlight] + '*' + @AWDP + '*UG'
WHERE STC1.STUDENT_ACAD_CRED_ID = STL1.STTR_SCHEDULE
GROUP BY STC1.STC_PERSON_ID

Shouldn't it be SUM(STC1....)?
Is it really one row only? Wouldn't you need to filter by TTAB.TA_STU_ID?
That doesn't mean GROUP BY TTAB.TA_STU_ID, as you had it, but: WHERE STC1.STC_PERSON_ID=TTAB.TA_STU_ID GROUP BY STC1.STC_PERSON_ID:

--this only works as integral part of the whole query:
(SELECT SUM(STC1.STC_CRED)
FROM STUDENT_ACAD_CRED STC1
JOIN STUDENT_TERMS_LS STL1 ON STL1.STUDENT_TERMS_ID = [highlight #FCE94F]STC1.STC_PERSON_ID[/highlight] + '*' + @AWDP + '*UG'
AND STC1.STUDENT_ACAD_CRED_ID = STL1.STTR_SCHEDULE
WHERE STC1.STC_PERSON_ID=[highlight #FCE94F]TTAB.TA_STU_ID[/highlight]
GROUP BY STC1.STC_PERSON_ID)

Besides all that, you sum STC1.STC_CRED, a field from STC1, not from STUDENT_TERMS_LS, so this will be N*STC1.STC_CRED, N depending on how many rows you join from STUDENT_TERMS_LS and at least N=1, even if there is no match in STUDENT_TERMS_LS. Is that, what you want? I would expect the SUM to be about the joined data.

Bye, Olaf.

 

[fredericofonseca]

this looks a bit wrong

select sum(stc.STC_CRED)
from STUDENT_ACAD_CRED stc1
join STUDENT_TERMS_LS stl1
    on stl1.STUDENT_TERMS_ID = ttab.TA_STU_ID + '*' + @AwDp + '*UG'
where stc1.STUDENT_ACAD_CRED_ID = stl1.STTR_SCHEDULE
group by ttab.TA_STU_ID

you are joining stc1 to st11 yet your join clause is linking to an outside table
If you were doing a join with just the 2 tables above how would you join them? That is what you need to put on the join clause.
And the linking to the outer table goes to either join or where clause but always after the correct joins

Then you wish to filter on the current value of "ttab.TA_STU_ID" so based just on the info you gave I think the correct should be this.
Note that on a correlated query like this the group by is not required and eventually not allowed at all.

declare @AwDp as varchar(10);
set @AwDp = '2016/SP';

declare @TatAble table -- which has been populated correctly
    ( TA_KEY      varchar(25)
    , TA_AMT      decimal(10)
    , TA_TX       decimal(10)
    , TA_AMT_DIFF decimal(10)
    , TA_ACTION   varchar(2)
    , TA_AWD      varchar(10)
    , TA_STU_ID   varchar(7)
    )

select distinct p.id
              , p.LAST_NAME
              , p.FIRST_NAME
              , @AwDp as 'Term'
              , awd.AW_TYPE
              , awd.AW_CATEGORY
              , awd.AW_ID
              , awd.AW_DESCRIPTION
              , ttab.TA_AMT
              , ttab.TA_TX
              , ttab.TA_AMT_DIFF
              , ttab.TA_ACTION
              ,

                -- getting the error on the following item

                (select sum(stc.STC_CRED)
                       from STUDENT_ACAD_CRED stc1
                       join STUDENT_TERMS_LS stl1
[highlight #EF2929]                           on stc1.STUDENT_ACAD_CRED_ID = stl1.STTR_SCHEDULE
                       where  stl1.STUDENT_TERMS_ID = ttab.TA_STU_ID + '*' + @AwDp + '*UG'[/highlight]
                ) reg_cred
              , sr.SAPR_TRM_REG_CRED
              , sr.SAPR_CALC_SAP_STATUS
              , sr.SAPR_OVR_SAP_STATUS

from @TatAble ttab

left join PERSON p
    on ttab.TA_STU_ID = p.id
left join AWARDS awd
    on ttab.TA_AWD = awd.AW_ID
left join FIN_AID_LS finl
    on p.id = finl.FA_STUDENT_ID
left join SAP_RESULTS sr
    on finl.FA_SAP_RESULTS_ID = sr.SAP_RESULTS_ID
    and sr.SAPR_CALC_THRU_TERM = @AwDp
left join STUDENT_TERMS_LS sttrl2
    on sttrl2.STUDENT_TERMS_ID = p.id + '*' + @AwDp + '*UG'
    and sttrl2.STTR_SCHEDULE is not null
left join STUDENT_ACAD_CRED stc
    on sttrl2.STTR_SCHEDULE = stc.STUDENT_ACAD_CRED_ID
where ttab.TA_AMT > 0
    and awd.AW_TYPE in ('F', 'O', 'S', 'O')

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Olaf Doschke]

On another note and therefor extra post: A query for a single field is a PITA, you would just generate the data you want to join in general for all students and the left join that on the student id match.

...

LEFT JOIN 
(
SELECT STC1.STC_PERSON_ID,
SUM(STC1.STC_CRED) as STC_CRED_SUM
FROM STUDENT_ACAD_CRED STC1
JOIN STUDENT_TERMS_LS STL1 ON STL1.STUDENT_TERMS_ID = STC1.STC_PERSON_ID + '*' + @AWDP + '*UG'
AND STC1.STUDENT_ACAD_CRED_ID = STL1.STTR_SCHEDULE
GROUP BY STC1.STC_PERSON_ID
) studentsum 
ON studentsum.STC_PERSON_ID=TTAB.TA_STU_ID

And then add studentsum.STC_CRED_SUM AS REG_CRED to the field list of the main query.

Bye, Olaf.

 

[ghbeers]

Well Olaf, I can't believe that I didn't see that typo. Yes that should have been stc1, not stc, and the query is now working correctly. I guess I was staring at this thing for so long that I failed to look at the obvious. Should have been the first thing I did. THANK YOU so much for seeing this. I am so sorry I took some of your time.

But I sure do appreciate all of the replies I received from everyone because they are very educational to me, someone who uses SQL as a secondary language. Thank you all.

 

[Olaf Doschke]

I'm very fine with this result.

This is actually NOT a waste of my time, but it helped, didn't it. It rather now makes me glad.
You see I also didn't notice before working on it for several times.

Finally, sometimes the MSSQL error message could point to more than error number/message and line number, especially telling the name it addresses and reports problems with.

Bye, Olaf.