CODEWITHSUNDEEP
sqldatabaseoracle-database
user5505661
user5505661

Reputation: 69

How to count the number of rows which have same starting letters in Oracle database

I have a table Customers:

╔════════════╦═════╗
║ CustomerID ║ ... ║
╠════════════╬═════╣
║ ACC20011   ║ ... ║
║ ACC30122   ║ ... ║
║ ACC12356   ║ ... ║
║ EVG12345   ║ ... ║
║ DAA23445   ║ ... ║
║ DAA23548   ║ ... ║
╚════════════╩═════╝

I need the following output:

╔════════════╦═══════╗
║ CustomerID ║ Count ║
╠════════════╬═══════╣
║    Acc     ║  3    ║
║    EVG     ║  1    ║
║    DAA     ║  2    ║
╚════════════╩═══════╝

I wrote the following query. But I know its wrong. Kindly correct me.

select CustomerID , count(*) as Count 
from Customers
where CustomerID is like 'ACC%'

Upvotes: 2

Views: 321

Answers (2)

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

The below query will also work if u have data format like 'A1234','ABCC2' etc 

  SELECT substr(CustomerID, 1, regexp_instr(CustomerID, '\d+') - 1) AS string
        ,count(*) as cnt
    FROM customer
    GROUP BY substr(CustomerID, 1, regexp_instr(CustomerID, '\d+') - 1)

Edit Another way to do the above in is with the use of regexp_replace

select regexp_replace(CustomerID,'\d+','') as string,count(*) cnt from customer
group by regexp_replace(CustomerID,'\d+','')

Above one replace all the digits present in your customerid.

Upvotes: 2

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521502

Try this query:

SELECT SUBSTR(CustomerID, 1, 3) AS CustomerID, COUNT(*)
FROM Customers
GROUP BY SUBSTR(CustomerID, 1, 3)

Upvotes: 9

Related Questions