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:
/*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
/*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*/
Any input is appreciated.