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');
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;