Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Cross tab with Oracle 10g

Re: Cross tab with Oracle 10g

From: <antonyliu2002_at_yahoo.com>
Date: 24 Feb 2007 12:10:12 -0800
Message-ID: <1172347811.956239.292710@j27g2000cwj.googlegroups.com>


On Feb 23, 2:44 pm, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> On Feb 22, 10:25 pm, "antonyliu2..._at_yahoo.com"
>
>
>
>
>
> <antonyliu2..._at_yahoo.com> wrote:
> > I am not a oracle DBA, I just need to get this straight for a web
> > application I am working on. I know only a little SQL as my major job
> > responsibility is Web development.
>
> > We have student counselors, each of them provides counseling service
> > for a bunch of students.
>
> > Each student counselor is supervised by one and only one faculty.
>
> > I was able to get something like below from two tables through a sql
> > query (blank lines are inserted only for your eyeballing convenience).
>
> > Couselor Stu_ID Supervisor
> > ------------- --------- -------------------
> > tmorris 999239434 pwilliams
>
> > waruw 997595754 buchanan
> > waruw 955557538 buchanan
> > waruw 992525897 buchanan
> > waruw 994974599 buchanan
> > waruw 995599938 buchanan
> > waruw 992724945 buchanan
> > waruw 954538288 buchanan
>
> > mstuart 992374925 jswenson
> > mstuart 997733949 jswenson
> > mstuart 954999558 jswenson
> > mstuart 995252853 jswenson
> > mstuart 953929733 jswenson
> > mstuart 958888249 jswenson
>
> > nnlee 994529457 hhall
> > nnlee 998287535 hhall
> > nnlee 992737242 hhall
> > nnlee 954995535 hhall
>
> > siemens 993324994 cjohnson
> > siemens 955554534 cjohnson
> > siemens 993475597 cjohnson
>
> > hpark 993949289 rjackson
> > hpark 992299445 rjackson
> > hpark 952292277 rjackson
> > hpark 993473859 rjackson
> > hpark 954459555 rjackson
>
> > I haven't had good luck in getting a crosstab from this data set.
> > Basically I would like to have something like this:
>
> > Counselor Supervisor Num_Of_Stu
> > --------------------------------
> > tmorris pwilliams 1
> > waruw buchanan 7
> > mstuart jswenson 6
> > nnlee hhall 4
> > siemens cjohnson 3
> > hpark rjackson 5
>
> > In other words, a count of students each student counselor serves. I
> > thought this could be easy, but quite often got a not a group by
> > expression or not a single-group group.
>
> > I googled, but could not find detailed tutorials on this. Any hint is
> > highly appreciated.
>
> Are you sure that this requires a crosstab? The other two answers
> provided to you are just what is needed if you do in fact need a
> crosstab. It appears to me that all you need is a simple GROUP BY,
> unless a counselor has more than one supervisor.
>
> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.- Hide quoted text -
>
> - Show quoted text -

Thank all of you for you hint. Yes, you are very likely right in saying that what I want may not be a crosstab at all. Indeed, what I did was to count the stu_id field and then group by the other fields.

Hey, do we always group by all fields except the counted one? It looks like if not, then I would get some error like "not a singlegroup  group".

Thx. Received on Sat Feb 24 2007 - 14:10:12 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US