Re: Frequency Count

From: <bdbafh_at_gmail.com>
Date: 7 Aug 2006 11:44:20 -0700
Message-ID: <1154976260.549109.199780@i3g2000cwc.googlegroups.com>

Charles Hooper wrote:
> fitzjarrell_at_cox.net wrote:
> > Charles Hooper wrote:
> > > Assuming in the above that the table name is MY_TABLE and the column
> > > names are PROD1, PROD2, PROD3, the following SQL statement should
> > > provide the desired output:
> > > SELECT
> > > 'PROD1',
> > > PROD1,
> > > COUNT(*) BUYERS
> > > FROM
> > > MY_TABLE
> > > GROUP BY
> > > PROD1
> > > UNION ALL
> > > SELECT
> > > 'PROD2',
> > > PROD2,
> > > COUNT(*) BUYERS
> > > FROM
> > > MY_TABLE
> > > GROUP BY
> > > PROD2
> > > UNION ALL
> > > SELECT
> > > 'PROD3',
> > > PROD3,
> > > COUNT(*) BUYERS
> > > FROM
> > > MY_TABLE
> > > GROUP BY
> > > PROD3
> > > ORDER BY
> > > 1,
> > > 2;
> > >
> > > As you can tell, this is not a very efficient SQL statement. It would
> > > be more efficient if the table was set up like this:
> > > PARENT_ID LINE_NO PRODUCT_ID AGE
> > >
> > > With the above table design, the SQL statement would look like this:
> > > SELECT
> > > PRODUCT_ID,
> > > AGE,
> > > COUNT(*) BUYERS
> > > FROM
> > > MY_TABLE
> > > GROUP BY
> > > PRODUCT_ID,
> > > AGE
> > > ORDER BY
> > > PRODUCT_ID,
> > > AGE;
> > >
> > > Charles Hooper
> > > PC Support Specialist
> > > K&M Machine-Fabricating, Inc.
> >
> > Analytic functions would make this easier:
> >
> > SQL> create table myprods (prod1 number, prod2 number, prod3 number);
> >
> > Table created.
> > SQL> insert into myprods values (34,23,45);
> >
> > 1 row created.
> >
> > SQL> insert into myprods values (34,22,34);
> >
> > 1 row created.
> >
> > SQL> insert into myprods values (54,44,45);
> >
> > 1 row created.
> >
> > SQL> insert into myprods values (23,22,45);
> >
> > 1 row created.
> >
> > SQL> insert into myprods values (45,22,34);
> >
> > 1 row created.
> >
> > SQL> commit;
> >
> > Commit complete.
> >
> > SQL> select prod1, count(prod1) over (partition by prod1) freq1,
> > 2 prod2, count(prod2) over (partition by prod2) freq2,
> > 3 prod3, count(prod3) over (partition by prod3) freq3
> > 4 from myprods;
> >
> > PROD1 FREQ1 PROD2 FREQ2 PROD3 FREQ3
> > ---------- ---------- ---------- ---------- ---------- ----------
> > 23 1 22 3 45 3
> > 34 2 23 1 45 3
> > 34 2 22 3 34 2
> > 45 1 22 3 34 2
> > 54 1 44 1 45 3
> >
> > SQL>
> >
> >
> > David Fitzjarrell

>

> Interesting, I was under the impression that the majority of the
> analytical functions were unavailable on the Standard Edition of
> Oracle.

This was covered in the 9i New Features Guide. As I ignored the 9i R1 release - I found it in the 9i R2 New Features Guide.

-bdbafh

> Your sample code executes as shown above. I guess that I now
> have something to read about over the weekend. :-)

>

> This tread reminded me of a program that I wrote a decade ago that was
> to keep a log of the various components of a couple hundred computers:
> CPU speed, memory, monitor size, hard drive size, RAM, etc. I was
> doing just fine entering the information into the program until I came
> across a computer with more than one hard drive... relational database,
> what is that? It looks like the original poster's table design is
> headed in the same direction.

>

> Charles Hooper
> PC Support Specialist
> K&M Machine-Fabricating, Inc.
Received on Mon Aug 07 2006 - 13:44:20 CDT