I am trying to get the total number of items and categorize them using case statements. However, I keep getting the items (which are now categorized as "inv") repeated (e.g. "clothes" is repeated 4 times, each with a count of 1 instead of just once with the total count of 4) - how do I get rid of that? I included the desired outcome below.

I am trying to get the total number of items and categorize them using case statements. However, I keep getting the items (which are now categorized as "inv") repeated (e.g. "clothes" is repeated 4 times, each with a count of 1 instead of just once with the total count of 4) - how do I get rid of that? I included the desired outcome below.

Here's my table:

TABLE BOX1

ITEM_NO ITEM DATE =================================== 3130C MARVEL_SHIRT 01-JAN-17 1845C SPONGEBOB_BOXERS 03-DEC-18 A900C CK_COAT 04-DEC-18 A988C RIDER_JEANS 11-JAN-19 L350T BARBIE 23-NOV-18 L129T LEGO 09-OCT-18 LXZYT BATMAN_FIG1 12-JAN-19

My code:

select (case substr (item_no,5,1) when 'C' then 'clothes' when 'T' then 'toys' else 'misc' end) inv, count(item_no) total from box1 where date <= sysdate group by item

Desired Outcome:

INV TOTAL =============== CLOTHES 4 TOYS 3

Learn to Build SQL Query| Ultimate SQL and DataBase Concepts

An Introduction to Oracle SQL: A quick tour of the six principal clauses of the SELECT statement.

Real time Oracle database administration interview questions