How to get count for similar identifier in a single column using PL/SQL

oracle sql total count group by
oracle count number of rows returned by query
oracle count group by
sql count
oracle sql sum multiple columns
oracle sql count if
sql select distinct values and count of each
oracle sql group by

I have a database with 1069 rows. There's a column called TRIAL_ID which has the person's identifier from different location. Following is a sample of the column:

TRIAL_ID
OAD001
OAD010
OAD501
ROT002
ROT008
ROT903
JAC3ODF
BA39SD
MK2093

If you notice, the first three are from location OAD and the second three are from the location ROT, and the rest with no pattern are from the third location.

What I want to do is show a count for each location. The following is what I tried:

select (
    select count(trial_id) from locations where trial_id like 'oad%'),
    (select count(trial_id) from locations where trial_id like 'rot%'),
    (select count(trial_id) from locations where trial_id not like 'rot%' or trial_id not like 'oad%') 
from locations

But this shows me the count of oad and rot correctly but not the third one. Also, it shows the count as many times as the number of rows in the locations table.

How can I only get the count for each type?

The reason that your version fails is because the or should be and. not and boolean logic is tricky.

However, conditional aggregation is a better approach:

select sum(case when trial_id like 'oad%' then 1 else 0 end) as oad,
       sum(case when trial_id like 'rot%' then 1 else 0 end) as rot,
       sum(case when trial_id not like 'oad%'  and trial_id not like 'rot%' then 1 else 0 end) as other
from locations;

It is better because it scans the table only once for all the calculations.

This query returns the results as a single row with three columns for the counts. Your version is going to show a separate row for each location, with the counts repeated on each row.

You can also express this with a group by:

select (case when trial_id like 'oad%' then 'oad'
             when trial_id like 'rot%' then 'rot'
             else 'other'
        end) as location,
       count(*)
from locations
group by (case when trial_id like 'oad%' then 'oad'
               when trial_id like 'rot%' then 'rot'
               else 'other'
          end);

This returns the results as (up to) three rows.

count, Database SQL Reference. Contents Purpose. COUNT returns the number of rows returned by the query. You can use it as an aggregate or analytic function. In this example SQL COUNT() function excludes the NULL values for a specific column if specified the column as an argument in the parenthesis of COUNT function. Sample table: listofitem To get the number of rows in the 'listofitem' table with the following condition -

A more generic one?

SQL> with test (trial_id) as
  2    (select 'OAD001' from dual union all
  3     select 'OAD010' from dual union all
  4     select 'OAD501' from dual union all
  5     select 'ROT002' from dual union all
  6     select 'ROT008' from dual union all
  7     select 'ROT903' from dual union all
  8     select 'JAC30F' from dual union all
  9     select 'BA395D' from dual union all
 10     select 'MK2093' from dual
 11    )
 12  select case when substr(trial_id, 1, 3) in ('OAD', 'ROT') then substr(trial_id, 1, 3)
 13              else 'no pattern'
 14         end location,
 15         count(*) cnt
 16  from test
 17  group by
 18         case when substr(trial_id, 1, 3) in ('OAD', 'ROT') then substr(trial_id, 1, 3)
 19              else 'no pattern'
 20         end;

LOCATION            CNT
------------ ----------
OAD                   3
ROT                   3
no pattern            3

SQL>

Having Sums, Averages, and Other Grouped Data, Database, SQL and PL/SQL All aggregate functions group data to ultimately produce a single result value. COUNT(*) does not ignore null values, whereas COUNT with a column input does. the result set to include only records that meet certain criteria, the GROUP BY clause can use a similar clause to filter groups. One way to do that is to nest aggreate functions: SELECT COUNT (MIN (0)) AS distinct_col3_col4_cnt FROM t GROUP BY col3, col4 ; Another way is to pad one (or both) columns to its maximum length. Depending on your data, this may not be a problem.

You need to use conditional aggregation here:

SELECT
    COUNT(CASE WHEN trial_id LIKE 'oad%' THEN 1 END) AS oad_cnt,
    COUNT(CASE WHEN trial_id LIKE 'rot%' THEN 1 END) AS rot_cnt,
    COUNT(CASE WHEN trial_id NOT LIKE 'oad%' AND trial_id NOT LIKE 'rot%'
               THEN 1 END) AS other_cnt
FROM locations;

Note: I chose to use COUNT here rather than SUM, because using the former allows us to avoid an explicit ELSE condition, thus leaving the code a bit shorter and tidier to read.

How to Find Duplicate Records in Oracle, Let's start by setting up a sample table for the demonstration. has duplicate records with the same information repeated in both fruit_name and color columns​. The COUNT () function returns the number of rows that matches a specified criterion. The AVG () function returns the average value of a numeric column. The SUM () function returns the total sum of a numeric column.

You have the wrong logical operator - you need to count the rows that don't start with rot and don't start with oad. Or, in SQL:

trial_id not like 'rot%' AND trial_id not like 'oad%'
// -- Here --------------^

Learn Oracle COUNT() Function By Practical Examples, This tutorial shows you how to use the Oracle COUNT() function to return the number Let's create a table named items that consists of a val column and insert some Secondly, the COUNT() function returns the number of the same last names for Finally, the HAVING clause returns only groups that have more than one  SQL COUNT ( ) with group by and order by . In this page, we are going to discuss the usage of GROUP BY and ORDER BY along with the SQL COUNT() function. The GROUP BY makes the result set in summary rows by the value of one or more columns. Each same value on the specific column will be treated as an individual group.

Oracle PL/SQL Programming, So, somewhere in this table, Oracle will have to come up with a place to store attribute It does this by adding hidden columns to the table, one column per unique In the same way that relational databases include unique identifiers for every row counting system: 1, 2, 828 || Chapter21: Object-Oriented Aspects of PL/SQL. select c1, c2, c3, c4, column_value cnt from country_test, table(cast(multiset( select count(distinct c1) from ( select c1 from dual union all select c2 from dual union all select c3 from dual union all select c4 from dual ) ) as sys.odcinumberlist));

SQL SELECT DISTINCT | COUNT | ROWS, DISTINCT can be used with aggregates: COUNT, AVG, MAX, etc. DISTINCT operates on a single column. DISTINCT for multiple columns is not supported. Select the table containing the column where you will count each duplicate, and click Kutools > Content > Advanced Combine Rows. 2. In the Advanced Combine Rows, select the column you will count each duplicate and click Primary Key, next select the column you will put counting results in and click Calculate > Count, and then click the OK button.

Beginning Oracle Application Express 5, Although you can still use APEX on table structures that use multicolumn natural have provided for free had the structures used one- or two-column surrogate keys. and start your sequence counting at a point above the highest current TICKET ID. Because it's primarily written in PL/SQL, APEX takes full advantage of  Identifiers of objects in a database, such as tables, views, and column names, are assigned the default collation of the database. For example, two tables with names that differ only in case can be created in a database that has case-sensitive collation, but cannot be created in a database that has case-insensitive collation.

Comments
  • Do you want a separate row for each location?
  • Just replace OR with AND operator here: '.... not like 'rot%' or trial_id not like .....
  • This is also a great solution. I agree with your statement that it's bit short and easier to understand. Thank you