Re: Frequency Count

From: <fitzjarrell_at_cox.net>
Date: 4 Aug 2006 07:26:49 -0700
Message-ID: <1154701609.239238.282050@75g2000cwc.googlegroups.com>

Charles Hooper wrote:
> Bertie Brink wrote:
> > I have three columns containing buyers ages for three products.
> >
> > prod 1 prod2 prod3
> >
> > 34 23 45
> > 34 22 34
> > 54 44 45
> > 23 22 45
> > 45 22 34
> >
> >
> > Can someone provide a SQL query to provide the frequency of every age
> > per column?
>
> 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 Received on Fri Aug 04 2006 - 09:26:49 CDT