Abdullah Feroz Abdullah Feroz -4 years ago 73
SQL Question

Listing Types in a Column with count Selecting all types whether the column value or not

I have a table for example Persons with person type(Private, Public, Government, Businessman, Jobless). Type can be null or Empty. I am saving type value in Person table with no Master Table. I want to select all grouped types with count by wheather the type exists or not.
I know it is a bit strange but the requirements are like this. Below are the scripts:

CREATE TABLE [Persons](
[ID] [int] IDENTITY(1,1) NOT NULL,
[FULLNAME] [varchar](50) NULL,
[TYPE] [varchar](10) NULL
) ON [PRIMARY]


INSERT INTO Persons(FULLNAME,[TYPE]) VALUES('ABD', 'Private');
INSERT INTO Persons(FULLNAME,[TYPE]) VALUES('DEF', 'Government');
INSERT INTO Persons(FULLNAME,[TYPE]) VALUES('DEF', 'Jobless');


The result should look like as follows:

TYPE COUNT
Private 1
Public 0
Government 1
Businessman 0
Jobless 1

Answer Source

You can create a Common Table Expression (CTE) of all the possible types using with clause and left join it with the persons table.

with types (type)
as (
    select 'Private' from dual
    union all
    select 'Public' from dual
    union all 
    select 'Government' from dual
    union all
    select 'Businessman' from dual
    union all 
    select 'Jobless' from dual
    )
select t.type,
    count(p.type) "COUNT"
from types t
left outer join Persons p on t.type = p.type
group by t.type;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download