CODEWITHSUNDEEP
sqlgoogle-bigquery
userag123
userag123

Reputation: 41

Equivalent of excel COUNTIFS

I am trying to get a COUNTIFS from excel type of result

Here is the products table:

Name Product

john    car
john    football
john    image
max     food
max     tv
max     laptop
max     image
max     image
max     image
alex    tv
alex    laptop
alex    image
alex    cake
alex    cake
alex    cake
alex    cake
alex    car

The output should be:

Name Product Number of products per person Number of products of the same type

john    car     1   2
john    football1   1
john    image   1   5
max     food    1   1
max     tv      1   2
max     laptop  1   2
max     image   3   5
alex    tv      1   2
alex    laptop  1   2
alex    image   1   5
alex    cake    4   4
alex    car     1   2

Number of products per person is count of products by name by product and Number of products of the same type is based on the total count by product

for example image is repeated 3 times for max so in col C the answer is 3 but it there 5 times in the table so answer in col D is 5

I tried but not getting the correct answer:

SELECT
  name,
  product,
  COUNT(*),
  COUNT(*) OVER (PARTITION BY product),
from products
GROUP BY 1,2
ORDER BY 1

Upvotes: 1

Views: 150

Answers (3)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use group by name and product

  SELECT  name,
      product,    
      COUNT(*),    
      COUNT(*) OVER (partition by product)

    from products

    GROUP BY name,product

    ORDER BY 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

You are quite close. You need to sum the COUNT(*). You can do this directly in the aggregation query:

SELECT name, product,
       COUNT(*),
       SUM(COUNT(*)) OVER (PARTITION BY product)
FROM products
GROUP BY 1, 2
ORDER BY 1

Upvotes: 1

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173003

#standardSQL
SELECT name, product, product_per_person,
  SUM(product_per_person) OVER(PARTITION BY product) product_total
FROM (
  SELECT
    name,
    product,
    COUNT(*) product_per_person
  FROM `project.dataset.products`
  GROUP BY 1,2
)
ORDER BY 1

if to apply to your sample data - result should be 

Row name    product     product_per_person  product_total    
1   alex    cake        4                   4    
2   alex    car         1                   2    
3   alex    image       1                   5    
4   alex    laptop      1                   2    
5   alex    tv          1                   2    
6   john    car         1                   2    
7   john    football    1                   1    
8   john    image       1                   5    
9   max     food        1                   1    
10  max     image       3                   5    
11  max     laptop      1                   2    
12  max     tv          1                   2

Upvotes: 1

Related Questions