Count same column from same table with different conditions

Question

I am using SQL on GBQ.

Here is a simplified version of my table :

Number | Amount | Name 4 10 A 1 0 B 6 100 A 8 0 C 5 0 A 4 10 C

None of the fields "Number", "Amount" or "Name" have unique values. They all can be repeated many times.

I want to calculate the occurrences where each "Name" has Amount=0 divided by the Total occurrence of that Name. Like the following : Count(Amount=0)/Count(Total)

For example :

For A : 1/3 = 0.33
For B : 1/1 = 1
For C : 1/2 = 0.5

To have the total occurrences by "Name", I could do the following query :

SELECT COUNT(*) Total, Name FROM MyTable GROUP BY Name

To count Amount=0 for each "Name" :

SELECT COUNT(*) TotalZero, Name FROM MyTable WHERE Amount>0 GROUP BY Name

How can I get both Total and TotalZero columns as a result of 1 query, to be able to divide them ?

I tried the following query :

SELECT A.TotalZero/A.Total FROM (SELECT COUNT(*) Total, Name FROM MyTable) A , (SELECT COUNT(*) TotalZero, Name FROM MyTable WHERE Amount>0) B GROUP BY A.Name

But I need a column to join the 2 tables on and I am not sure which one I should use. Any help would be useful.

Note : The table in my example above is actually the result of a sub-query.

Gordon Linoff · Accepted Answer · 2021-01-18 15:18:20Z

1

One method is countif():

SELECT Name, COUNTIF(Amount = 0) * 1.0 / COUNT(*) FROM MyTable GROUP BY Name;

Another method is conditional aggregation using AVG():

SELECT Name, AVG(CASE WHEN Amount = 0 THEN 1.0 ELSE 0 END) FROM MyTable GROUP BY Name;

answered Jan 18, 2021 at 15:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

HelpASisterOut Over a year ago

Thank you for the reply. It didn't work because COUNTIF(Amount=0) always returns 0 for all 'Names'.

HelpASisterOut Over a year ago

I forgot to mention that the table is a subquery. Could that be the reason ?

Gordon Linoff Over a year ago

@HelpASisterOut . . . if countif() returns 0, then there are no cases where amount = 0. Whether you are querying from a table or subquery doesn't matter.

HelpASisterOut Over a year ago

Yes indeed it was a problem in my subquery - works now thanks !

Collectives™ on Stack Overflow

Count same column from same table with different conditions

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related