Link to home
Start Free TrialLog in
Avatar of Rakafkaven
Rakafkaven

asked on

SQL: COUNT each of several values in one field

I'm convinced there's an easy way to do this, but heck if I can figure it out.  My environment is Oracle.

I have a query that aggregates some of the data from a table so that the query result is a single row, like this:

TTL_COUNT | AVG_PRICE  |  AVG_AGE  |  TTL_PRICE  
       15        |       500       |       25       |       32672  

I have another field ('GRADE'), and I'd like to show the count of each value in that field (A-D).  So my result would look like:

TTL_COUNT | AVG_PRICE  |  AVG_AGE  |  TTL_PRICE  |  COUNT_A  | COUNT_B | COUNT_C | COUNT_D
       15        |       500       |       25       |       32672    |       6         |       8       |       0       |       1

The only way I've found make this work is with a subquery in my FROM clause for each value, which gets ridiculously long:

SELECT
   /*existing code above here*/
  COUNT(GRADE_A.key) AS COUNT_A,
  COUNT(GRADE_B.key) AS COUNT_B,
  COUNT(GRADE_C.key) AS COUNT_C,
  COUNT(GRADE_D.key) AS COUNT_D
FROM
  main_table,
  (SELECT key
     FROM main_table
     WHERE GRADE='A'
  ) GRADE_A,
  (SELECT key
     FROM main_table
     WHERE GRADE='B'
  ) GRADE_B,
  (SELECT key
     FROM main_table
     WHERE GRADE='C'
  ) GRADE_C,
  (SELECT key
     FROM main_table
     WHERE GRADE='D'
  ) GRADE_D
WHERE 1=1
  /*criteria for limiting records above here*/
  /*what follows are the joins that limit the subqueries to the same criteria the rest of the query uses*/
  AND main_table.key=GRADE_A.key(+)
  AND main_table.key=GRADE_B.key(+)
  AND main_table.key=GRADE_C.key(+)
  AND main_table.key=GRADE_D.key(+)


Any input is appreciated.
Avatar of Rakafkaven
Rakafkaven

ASKER

If the lines at the bottom are confusing any non-Oracle users, think of them as LEFT JOINs from the main_table to the four subtables.  I'm assuming that if there is a better solution, it won't necessarily be Oracle-specific, which is why I tried this on the main DB page.
ASKER CERTIFIED SOLUTION
Avatar of Ritesh_Garg
Ritesh_Garg
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
RacafKaven,

You could do something like,

Select count(*),ColumnName from TABLENAME

Group by ColumnName

It will answer you questions.

Best Regads,

Paulo Condeça.
Ritesh, that's perfect!  Don't know why I never think of using conditionals in my SQL.