CODEWITHSUNDEEP
sqloracle-databasecountgroup-by
Lars Mertens
Lars Mertens

Reputation: 1439

Case SQL and count each row

How can I get the required results below? I could get all unique categories by adding DISTINCT, but in retrieving the total of each category the query below doesn't work.

Table structure: BEER

ID | NAME | TYPE | ALCOHOL |

Required Result

category   |  total
----------------------------
Light      | 34
Medium     | 2
Normal     | 3
Heavy      | 4
Knock out  | 5

My SQL Query:

SELECT 
   CASE WHEN b.ALCOHOL < 3 THEN 'Light'
        WHEN b.ALCOHOL < 5 THEN 'Medium'
        WHEN b.ALCOHOL < 7 THEN 'Normal'
        WHEN b.ALCOHOL < 9 THEN 'Heavy'
        WHEN b.ALCOHOL >= 9 THEN 'Knock out'
   END AS category
FROM BEER b;

Could anyone steer me in the right direction?

Upvotes: 1

Views: 73

Answers (4)

Bohemian
Bohemian

Reputation: 425033

Add a count and group by:

SELECT Category, COUNT(*) AS Total FROM (
  SELECT 
    CASE WHEN b.ALCOHOL < 3 THEN 'Light'
         WHEN b.ALCOHOL < 5 THEN 'Medium'
         WHEN b.ALCOHOL < 7 THEN 'Normal'
         WHEN b.ALCOHOL < 9 THEN 'Heavy'
         ELSE 'Knock out'
    END AS Category
  FROM BEER) b
GROUP BY Category

The subquery is used to simplify the GROUP BY, because Oracle doesn't support GROUP BY 1 syntax.

Also note the simpler ELSE in the CASE

Upvotes: 2

Gurwinder Singh
Gurwinder Singh

Reputation: 39477

you can apply GROUP BY on the CASE and find COUNT

SELECT 
   CASE WHEN b.ALCOHOL < 3 THEN 'Light'
        WHEN b.ALCOHOL < 5 THEN 'Medium'
        WHEN b.ALCOHOL < 7 THEN 'Normal'
        WHEN b.ALCOHOL < 9 THEN 'Heavy'
        WHEN b.ALCOHOL >= 9 THEN 'Knock out'
   END AS category,
   count(*) total
FROM BEER b
GROUP BY 
    CASE WHEN b.ALCOHOL < 3 THEN 'Light'
        WHEN b.ALCOHOL < 5 THEN 'Medium'
        WHEN b.ALCOHOL < 7 THEN 'Normal'
        WHEN b.ALCOHOL < 9 THEN 'Heavy'
        WHEN b.ALCOHOL >= 9 THEN 'Knock out'
   END;

Upvotes: 1

Rahul
Rahul

Reputation: 77876

Use a aggregate function with the same condition along with group by like

sum(CASE WHEN b.ALCOHOL < 3 THEN 1 else 0 end)
....
from tbl1
group by some_col

Upvotes: 0

JohnHC
JohnHC

Reputation: 11195

You need a count() and a group by. I have used a CTE to avoid a brutal group by

with CTE as
(
SELECT 
   CASE WHEN b.ALCOHOL < 3 THEN 'Light'
            WHEN b.ALCOHOL < 5 THEN 'Medium'
                WHEN b.ALCOHOL < 7 THEN 'Normal'
                WHEN b.ALCOHOL < 9 THEN 'Heavy'
                WHEN b.ALCOHOL >= 9 THEN 'Knock out'
   END AS category,
   b.Alcohol
     FROM BEER b
)
select category, count(alcohol)
from CTE
group by category

Upvotes: 3

Related Questions