CODEWITHSUNDEEP
sqlgroup-bygoogle-bigqueryaggregate-functions
alcor
alcor

Reputation: 565

How to count elements with condition in a table in Big Query

I have a table like this in Big Query:

+-----+-----+-----+
| IDm |  A  |  B  |
+-----+-----+-----+
| 102 | 0.9 | 0.3 |
| 100 | 0.3 | 0.1 |
| 199 | 0.1 | 0.6 |
| 102 | 0.2 | 0.9 |
| 102 | 0.4 | 0.3 |

The "IDm" items are not unique, there are multiple "IDm" with the same value with different "A" and "B" values. I want to know how many times every single "IDm" satisfies the condition "A+B > 1". How can i do this? I usualy use Python and Pandas to do this, but is there an SQL way to do it? I'd like to run the query on the Big Query web interface.

Upvotes: 1

Views: 2108

Answers (3)

Salman Arshad
Salman Arshad

Reputation: 272136

Use conditional aggregation:

SELECT IDm, COUNT(CASE WHEN A + B > 1 THEN 1 END) AS cond_match_count
FROM tbl
GROUP BY IDm

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173003

Below is for BigQuery Standard SQL 

#standardSQL
SELECT IDm, COUNTIF(A + B > 1 ) qualified_counts
FROM `project.dataset.table`
GROUP BY IDm

If to apply to sample data from your question - result is 

Row IDm qualified_counts     
1   102 2    
2   100 0    
3   199 0

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You would use countif():

select countif( a + b > 1)
from t;

Or:

select count(*)
from t
where a + b > 1;

If you have duplicate idm values and want a unique count:

select count(distinct idm)
from t
where a + b > 1;

Upvotes: 1

Related Questions