CODEWITHSUNDEEP
sqlpostgresqlpivot
nitinkhatri749
nitinkhatri749

Reputation: 85

I want to split Categorial Value into different fields in sql

Data is like this- State, district, category(Only two fields - CLEAR and SUSPECT). I want to get the data in format(Table-2)

Table-1

State,DC,     cat2
ASSAM KAMRUP CLEAR
ASSAM KAMRUP CLEAR
ASSAM KAMRUP SUSPECT
ASSAM KAMRUP CLEAR
ASSAM Cachar CLEAR
ASSAM Cachar CLEAR
BIHAR Buxar  SUSPECT
BIHAR Buxar  CLEAR
BIHAR Buxar  CLEAR
BIHAR Buxar  SUSPECT
BIHAR Buxar  CLEAR
BIHAR Buxar  SUSPECT
BIHAR Buxar  CLEAR

I want something like this- Table-2

State DC     CLEAR        SUSPECT
ASSAM KAMRUP count(CLEAR) count(SUSPECT)
ASSAM Cachar count(CLEAR) count(SUSPECT)
BIHAR BUXAR  count(CLEAR) count(SUSPECT)

Upvotes: 1

Views: 28

Answers (2)

zealous
zealous

Reputation: 7503

You can use sum with case statement as well

select
  state,
  DC,
  sum(case when cat2 = 'CLEAR' then 1 else 0 end) as clear,
  sum(case when cat2 = 'SUSPECT' then 1 else 0 end) as suspect
from yourTable
group by 
  state,
  DC

Upvotes: 1

user330315
user330315

Reputation:

In Postgres you can use filtered aggregation for that:

select state, dc, 
       count(*) filter (where cat2 = 'CLEAR') as clear, 
       count(*) filter (where cat2 = 'SUSPECT') as suspect
from the_table
group by state, dc;

Upvotes: 3

Related Questions