On Feb 25, 10:26 am, "Charles Hooper" <hooperc2..._at_yahoo.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
Hokay, great! Thanks a lot. Will check out the group by wiki and see
if I can create group by statement arbitrarily for anything.
Received on Mon Feb 26 2007 - 11:50:40 CST
