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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
RacafKaven,
You could do something like,
Select count(*),ColumnName from TABLENAME
Group by ColumnName
It will answer you questions.
Best Regads,
Paulo Condeça.
You could do something like,
Select count(*),ColumnName from TABLENAME
Group by ColumnName
It will answer you questions.
Best Regads,
Paulo Condeça.
ASKER
Ritesh, that's perfect! Don't know why I never think of using conditionals in my SQL.
ASKER