CODEWITHSUNDEEP
sqlcountteradata
BCM
BCM

Reputation: 63

Teradata SQL - two counts from same column

I'm new to SQL and Teradata. I tried searching for an answer to this but have been unsuccessful so far.

I created a volatile table with a column that holds a value of <100 (can include negatives). I want to get a total count, a count of all "0"values, a count of all values where 0

I know I could just do a separate statement for each type but for my own knowledge I want to learn the "proper" way. I tried using a similar solution found at this link - Different value counts on same column - but that doesn't seem to work since I need to use >, <, and similar operators.

A layout similar to the table in that thread would be sufficient.

Thanks for the help.

Upvotes: 0

Views: 1511

Answers (1)

juergen d
juergen d

Reputation: 204766

count cannot be conditional. It counts all records or if you specify a column, all records where the column is not null. use sum

select count(*) as total,
       sum(case when some_col = 0 then 1 else 0 end) as count_0,
       sum(case when some_col > 0 then 1 else 0 end) as count_above0
from your_table

Upvotes: 2

Related Questions