CODEWITHSUNDEEP
sqlgoogle-bigquery
HelpASisterOut
HelpASisterOut

Reputation: 3185

Count same column from same table with different conditions

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 :

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.

Upvotes: 0

Views: 1861

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

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;

Upvotes: 1

Related Questions