CODEWITHSUNDEEP
oracle-sqldeveloper
llamaro25
llamaro25

Reputation: 692

Select statement based on category

I have a table like this 

Code 
A123
B3123
C93485
D345
E29845

The first letter of rows in code column are classified into the following:

Char Category
A-B      A
C        B
D-E      C

I would like to display the output table like this 

Category Total Percentage
A          2     0.4%
B          1     0.2%   
C          2     0.4%
     Total 5     1.0%

I'm not sure how to start. Any hints or help is much appreciated

Upvotes: 1

Views: 19

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522346

Here is one option:

SELECT
    CASE WHEN SUBSTR(Code, 1, 1) IN ('A', 'B') THEN 'A'
         WHEN SUBSTR(Code, 1, 1) = 'C' THEN 'B'
         ELSE 'C' END AS Category,
    COUNT(*) AS Total,
    200.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS Percentage
FROM yourTable
GROUP BY
    ROLLUP(CASE WHEN SUBSTR(Code, 1, 1) IN ('A', 'B') THEN 'A'
         WHEN SUBSTR(Code, 1, 1) = 'C' THEN 'B'
         ELSE 'C' END);

screen capture of demo below

Demo

This approach uses a CASE expression on the first letter of each code to assign a category. Then, we aggregate by category and find the totals, as well as the percentages. Note that ROLLUP is used to generate a total record at the bottom of the result set. As a side effect of this, we multiply by 200%, because the summary row already contains the entire table count, which then gets counted twice.

Upvotes: 1

Related Questions