Re: Cross tab with Oracle 10g

On Feb 24, 3:10 pm, "antonyliu2..._at_yahoo.com" <antonyliu2..._at_yahoo.com> wrote:
> 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.
>
> 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 single-
> group group".
>

Yes, unless you are using the analytical versions of COUNT, you need to GROUP BY all columns that are not included inside a COUNT(), MIN(), MAX, AVG(), etc. Analytical example:
  SELECT
    COUSELOR,
    SUPERVISOR,
    COUNT(*) OVER (PARTITION BY COUSELOR) NUM_OF_STU   FROM
    T1;

In the above, you will note that there is no GROUP BY clause, but you will receive many duplicated rows. You would then need to adjust the query like this by sliding the above into an inline view and selecting distinct records from the inline view:
SELECT DISTINCT
  COUSELOR,
  SUPERVISOR,
  NUM_OF_STU
FROM
  (SELECT
    COUSELOR,
    SUPERVISOR,
    COUNT(*) OVER (PARTITION BY COUSELOR) NUM_OF_STU   FROM
    T1);

You will likely find that the above method executes slower than a simple GROUP BY.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Feb 25 2007 - 10:26:44 CST