CODEWITHSUNDEEP
mysqlsqlhiveql
Jack Daniel
Jack Daniel

Reputation: 2611

SQL - Filter on two columns

I am trying to get of the count of two columns with a where clause on each column individually in sql.

Lets say my data looks like

person feature1   feature2
a       1           1
a       0           1
a       1           1
a       1           1
a       0           0
a       1           1
b       0           1
c       1           0

Now, I want to group the data by person and the grouped data should look like

  person feature1   feature2
    a       2           1
    b       0           1
    c       1           0

I wanted to count the no of zeros of each column per person. How can I do this through sql.

Upvotes: 0

Views: 127

Answers (2)

Guru0008
Guru0008

Reputation: 54

Here you can use case statement to count non zero features for each person

select person, count(case when feature1>0 then 1 else null end) F1, count(case when feature1>0 then 1 else null end) F2 from Table1 group by person ;

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use conditional aggregation to do this. Conditions in sum return 1 or 0 depending on true or false. 

select person,sum(feature1=0),sum(feature2=0)
from tbl
group by person

In Hive, you should cast the boolean returned to intbefore summing up.

select person,sum(cast(feature1=0 as int)),sum(cast(feature2=0 as int))
from tbl
group by person

Upvotes: 3

Related Questions