Why does this subquery need an inner join?

Question

question

Dang asked Feb 21, '13

Why does this subquery need an inner join?

I'm trying to use some aggregate functions in a subquery, referencing a table that is already joined in the outer query, but for some reason the query throws an error unless I join the table directly in the inner query.

-- creating data structure
create table categories (
	catId int identity (1,1) primary key,
	catName varchar(30)
)
insert categories values ('cat1')
insert categories values ('cat2')

create table activities (
	actId int identity (1,1) primary key,
	actName varchar(30),
	catId int foreign key references categories (catId)
)
insert activities values ('act1', 1)
insert activities values ('act2', 2)
insert activities values ('act3', 2)

create table subActivities_states (
	subActStId int identity (1,1) primary key,
	subAtcStName varchar(30)
)
insert subActivities_states values ('not started')
insert subActivities_states values ('ongoing')
insert subActivities_states values ('finished')

create table subActivities (
	subActId int identity (1,1) primary key,
	subActName varchar(30),
	subActStId int foreign key references subActivities_states (subActStId),
	actId int foreign key references activities (actId)
)
insert subActivities values ('act1-subAct1-not started', 1, 1)
insert subActivities values ('act1-subAct2-ongoing', 2, 1)
insert subActivities values ('act1-subAct3-finished', 3, 1)
insert subActivities values ('act2-subAct1-not started', 1, 2)
insert subActivities values ('act2-subAct2-ongoing', 2, 2)
insert subActivities values ('act3-subAct1-not started', 1, 3)
insert subActivities values ('act3-subAct2-finished', 3, 3)

go

-- selecting total subactivities and total finished subactivities by category
select catName,
	SUM(subCnt) subCntTotal,
	SUM(subCntFinished) subCntFinishedTotal
from (
	select catName,
		COUNT(sub.subActId) subCnt,
		(
			select COUNT(1)
			from subActivities_states st
				/* without this join, the query throws an error */
				join subActivities sub on sub.actId = act.actId
			where st.subActStId = sub.subActStId
				and st.subAtcStName = 'finished'
		) subCntFinished
	from activities act
		join categories cat on cat.catId = act.catId
		join subActivities sub on sub.actId = act.actId
	group by catName, act.actId
) subQuery
group by catName

go

-- dropping tables
drop table subActivities
drop table subActivities_states
drop table activities
drop table categories

sql t-sql tsql aggregates sub-query

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2013-02-21T16:47:34Z

KenJ answered Feb 21, '13

Msg 8120, Level 16, State 1, Line 14 Column 'subActivities.subActStId' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Because you are using the sub.subActStId column above the FROM clause, it must also be in the GROUP BY clause. By adding the JOIN, you now have a "sub" alias that is local to the subquery so it doesn't have to play by the rules of the outer query. It is also no longer correlated to the outer query, so the finished counts may not meet your expectations. I like to pull in counts via a JOIN, so I would probably get the finished count numbers like this: LEFT JOIN (SELECT st.subActStId, COUNT(1) AS FinishedCount FROM subActivities_states st WHERE st.subAtcStName = 'finished' GROUP BY st.subActStId) finished then just use `COUNT(finished.FinishedCount) subCntFinished` in place of the original subquery

1

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Dang commented · Feb 21, 2013 at 05:25 PM

Thanks a lot for the explanation. Your solution worked perfectly!

0 ·

question