Re: Frequency Count

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. 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 Fri Aug 04 2006 - 18:13:32 CDT