x

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

more ▼

asked Feb 21, 2013 at 02:46 PM in Default

avatar image

Dang
50 1 1 5

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
 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

more ▼

answered Feb 21, 2013 at 04:47 PM

avatar image

KenJ
25k 3 13 20

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

Feb 21, 2013 at 05:25 PM Dang
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1208
x1096
x454
x76
x54

asked: Feb 21, 2013 at 02:46 PM

Seen: 1538 times

Last Updated: Feb 21, 2013 at 06:04 PM

Copyright 2018 Redgate Software. Privacy Policy