Derived Groups? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Derived Groups?

Author

Topic

mikebird
Aged Yak Warrior

529 Posts

Posted - 2011-08-12 : 05:05:53

Please help.

Have to get the syntax right in a few minutes for meeting...
The gap near the top is to test each one seperately. Then I need to take StudentID (GUID) and its count columns to join with all other tables after attendance.

Just wondering why selecting down to the derived table b gives error:
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ')'.

Thanks!

select * from
(select studentID, Present, count(*) count
from studentAttendance sa
group by StudentID, present) p
full join b on b.studentID = p.StudentID

(select StudentID, Behaviour, count(*) count
from studentAttendance sa
group by StudentID, behaviour) b

(select Contribution, count(*) count
from studentAttendance sa
group by StudentID, contribution) c

(select StudentID, Late, count(*) count
from studentAttendance sa
group by StudentID, late) l

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-12 : 05:27:57

[code]
select * from
(select studentID, Present, count(*) count
from studentAttendance sa
group by StudentID, present) p
full join ~~b on b.studentID = p.StudentID~~

(select StudentID, Behaviour, count(*) count
from studentAttendance sa
group by StudentID, behaviour) b
on b.studentID = p.StudentID
[/code]

KH
[spoiler]Time is always against us[/spoiler]

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-08-12 : 05:28:43

What is b at the time of the full join? If b is the derived table below, the whole statement needs to be in there instead of the alias.

select * from
(select studentID, Present, count(*) count
from studentAttendance sa
group by StudentID, present) p
full join
(select StudentID, Behaviour, count(*) count
from studentAttendance sa
group by StudentID, behaviour) b
on b.studentID = p.StudentID

mikebird
Aged Yak Warrior

529 Posts

Posted - 2011-08-12 : 05:31:36

Got it before your replies.

Always do!!

Thanks!

select * from
(select studentID, Present, count(*) count
from studentAttendance sa
group by StudentID, present) p

join (select StudentID, Behaviour, count(*) count
from studentAttendance sa
group by StudentID, behaviour) b on b.studentID = p.StudentID

join (select studentID, Contribution, count(*) count
from studentAttendance sa
group by StudentID, contribution) c on c.StudentID = b.StudentID

join (select StudentID, Late, count(*) count
from studentAttendance sa
group by StudentID, late) l on l.StudentID = c.StudentID

mikebird
Aged Yak Warrior

529 Posts

Posted - 2011-08-12 : 05:37:59

Yep - all obvious. I just love aliases. I remembered you have to be explicit with a query - can't just give it a name. Like with a having clause - can't supply an alias - whole query in there. Can slow you down reading through someone else's scripts - you have to wrap each section in your own head-paranthesis - wish aliasing was easier...

mikebird
Aged Yak Warrior

529 Posts

Posted - 2011-08-12 : 06:23:24

Can I ask again, please?

I have everything now, leaving grouping for later.

I want to take the whole query and put it into another derived table a and use its studentID to link to the mighty student table. Why the error? I need to get the parenthesis right...

Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ')'.

select present, behaviour, contribution, late
from
(select studentID, Present
from studentAttendance sa) p

join (select StudentID, Behaviour
from studentAttendance sa) b on b.studentID = p.StudentID

join (select studentID, Contribution
from studentAttendance sa) c on c.StudentID = b.StudentID

join (select StudentID, Late
from studentAttendance sa) l on l.StudentID = c.StudentID) a
join student s on s.ID = a.studentID

Sachin.Nand

2937 Posts

Posted - 2011-08-12 : 06:36:03

[code]
...join ((select StudentID, Late
from studentAttendance sa) l on l.StudentID = c.StudentID) a
join student s on s.ID = a.studentID
[/code]

PBUH

mikebird
Aged Yak Warrior

529 Posts

Posted - 2011-08-12 : 06:59:59

quote:

Originally posted by Sachin.Nand


...join ((select StudentID, Late
from studentAttendance sa) l on l.StudentID = c.StudentID) a
join student s on s.ID = a.studentID

PBUH

I like this one. Again, I came up with a solution which seemed to work OK, without using a. This surprised me - is seems there is another way to achieve the same goal - I thought there'd be an error on one method... I check yours in a minute.

This one is fine:

alter view summary2 as

select con.firstname + ' ' + con.lastname Student,
--p.studentID,
present,
behaviour,
contribution,
late
from
(select studentID, Present
from studentAttendance sa) p

join (select StudentID, Behaviour
from studentAttendance sa) b on b.studentID = p.StudentID

join (select studentID, Contribution
from studentAttendance sa) c on c.StudentID = b.StudentID

join (select StudentID, Late
from studentAttendance sa) l on l.StudentID = c.StudentID
left join student s on s.ID = p.studentID
join contact con on con.ID = s.contactID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources