How to use count using case statements

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|Simpliv

Learn to Build SQL Query| Ultimate SQL and DataBase Concepts|Simpliv

Learn to Build SQL Query| Ultimate SQL and DataBase Concepts

Description
SQL developers are earning higher salary in IT industry, but, its not about writing queries its about understanding and applying the right query at right time and this course will let you understand complex SQL Statements in an easy way .

Moreover, This Course will teach you how to extract Data from Database and write complex queries to a database This course will focus on a wider scale by Covering Structure Query Language SQL concepts as a whole, whether Students work with MySQL, Microsoft SQL Server, Oracle Server, etc.

This course have 5 Chapters in which you will learn

Chapter 1 Fundamentals

Fundamentals
Building Blocks
Selecting Records from DB
Working with Arithmetic Expressions
Chapter 2 Conditioning Sorting and Operators

Logical Operators
Comparison Operators
Operator Precedence
Sorting Results
Chapter 3 Functions

Character Functions
Number Functions
Date Functions
Conversions
General Purpose Functions
Nesting Functions
Chapter 4 Grouping

Multiple Row Functions on a single Table
Multiple Row Functions on Many Table
Chapter 5 Joins

Understanding Primary Key
Understanding Foreign Key
Understanding Need of Joins
Cartesian Product
Equie Join Simple Join Self Join
Non Equie Join
Outer Join
Self Join
Course is Designed for College and University Students who want Solid SQL and Data Base Concepts in a short period of time.

Who this course is for:

Beginners
University or College students
Anyone who wants Solid SQL Concepts
Basic knowledge
No prior knowledge is required
PC or MAC
What will you learn
SQL Fundamentals
Understand Complex SQL Concepts in Easy way using daily life examples
Construct SQL Statements
Use SQL to retrieve data from database
Selecting Data From Database
Restricting and Sorting Data from DB
Grouping Data From DB
Construct SQL statements that will let them work with more than two tables
Use SQL Functions
Work with SQL Operators and find out precedence
Nesting in SQL
Joins
To continue:

An Introduction to Oracle SQL

An Introduction to Oracle SQL

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

This course is a brief presentation of Oracle SQL.
We will install a version of Oracle Express Edition and Oracle SQL Developer for free.
We'll then look at the SELECT statement with its 6 main clauses (SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY).
Following this, we'll look at what else you need to know, and resources (which range from FREE to very expensive) which will help you to learn it..

What you'll learn

  • Either Download and Install Oracle Express Edition and Oracle SQL Developer on your computer - for free
  • Write your own SELECT statements.
  • Learn the SELECT, FROM, WHERE, GROUP BY, HAVING and ORDER BY clauses.
  • Get an easy way to remember the order of these clauses.
  • Find out what more you need to know, and resources in how to learn it.

Real time Oracle database administration interview questions | Simpliv

Real time Oracle database administration interview questions | Simpliv

Real time Oracle database administration interview questions

Description
Course targeting to those students who wanted to start there carrier as Oracle Database administration. I have covered most of the topic including real time scenario. I will keep posted the same on your demand.

Please send me mail on below email ID if you have any queries. I will try to solve the same as soon as possible.

Following are the interview questions are covered along with practical solution. All the questions are frequently asked in interview.

Core DBA
RAC(Real application cluster)
Dataguard
ASM(Automatic Storage management)
Oracle 12C Administration
RMAN Backups
Logical Backups
Monitoring Tools
Basic understanding of ITIL Process
Course Feature

All videos contain the real time scenario which you will face in daily DBA routine. We create the problem and then provide the way to get the solution
New Videos will be added to course on Daily or Weekly basis
All the interview questions are frequently asked in interview
Answers are provided in with practical presentation
On Demand Videos will be added
you can reach directly vie mail or message for any technical query
Who is the target audience?

Student and Experienced folks looking forward to crack Oracle DBA interview
Basic knowledge
Basic knowledge of SQL and Linux. We are here to help you out for more understanding
Laptop or PC with 2GB RAM
What will you learn
Easily Crack oracle database interview with real time interview question
Oracle database administration
To continue: