How to write an SQL query to get the account of the distinct object

advertisements

There is a table "OBJECTS" with one column "OBJECT_NAME" having three values OBJ1, OBJ2, OBJ3. Snapshot of the table OBJECTS is:

OBJECT_NAME
----------
OBJ3
OBJ1
OBJ2
OBJ1
OBJ1
OBJ2

how to write a efficient SQL query to get output the count of the distinct object in the format as shown below.

OBJ1_Count OBJ2_COUNT OBJ3_COUNT
---------------------------------
3              2           1


Try this:

select
  sum(case when [object_name] = 'OBJ1' then 1 else 0 end) as obj1_count,
  sum(case when [object_name] = 'OBJ2' then 1 else 0 end) as obj2_count,
  sum(case when [object_name] = 'OBJ3' then 1 else 0 end) as obj3_count
from
  [objects]

An index on object_name would definitely be beneficial