Re: Frequency Count

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. Received on Fri Aug 04 2006 - 07:42:21 CDT