CODEWITHSUNDEEP
sqlselectgoogle-bigquery
rbkk2016
rbkk2016

Reputation: 215

how to filter certain records from my table - bigquery

I have a table that has product_name,groups(a,b,c,d) I want to get all products that are in groups a,b and d but not c.

I gave below query but I am not getting expected results

SELECT
prod_name,group, COUNT(group) AS Count
FROM
  [prod.table]
WHERE
prod NOT IN('c')
AND prod IN('a'.'b','d')

table below

From the above table, the query has to fetch only prod2 as it is not in group c.

I can do this easily using pivot table by just excluding group c alone and including a,b,d. I am looking for something similar.

Upvotes: 0

Views: 97

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172944

Try below 

SELECT
  product, GROUP_CONCAT_UNQUOTED(gr) groups, COUNT(gr) AS cnt
FROM (
  SELECT product, gr FROM 
    (SELECT 'Prod1' AS product,'a' AS gr), 
    (SELECT 'Prod1' AS product,'b' AS gr),
    (SELECT 'Prod1' AS product,'c' AS gr), 
    (SELECT 'Prod1' AS product,'d' AS gr), 
    (SELECT 'Prod2' AS product,'a' AS gr), 
    (SELECT 'Prod2' AS product,'b' AS gr), 
    (SELECT 'Prod2' AS product,'d' AS gr), 
    (SELECT 'Prod3' AS product,'a' AS gr), 
    (SELECT 'Prod3' AS product,'b' AS gr), 
    (SELECT 'Prod3' AS product,'c' AS gr), 
    (SELECT 'Prod3' AS product,'d' AS gr)
) 
GROUP BY product 
HAVING NOT ',' + groups + ',' CONTAINS ',c,'
AND ',' + groups + ',' CONTAINS ',a,'
AND ',' + groups + ',' CONTAINS ',b,'
AND ',' + groups + ',' CONTAINS ',d,'

but I'm looking for count of products that are not in group c but in other groups.

SELECT
  COUNT(product) AS products
FROM (
  SELECT
    product, GROUP_CONCAT_UNQUOTED(gr) groups
  FROM (
    SELECT product, gr FROM 
      (SELECT 'Prod1' AS product,'a' AS gr), 
      (SELECT 'Prod1' AS product,'b' AS gr),
      (SELECT 'Prod1' AS product,'c' AS gr), 
      (SELECT 'Prod1' AS product,'d' AS gr), 
      (SELECT 'Prod2' AS product,'a' AS gr), 
      (SELECT 'Prod2' AS product,'b' AS gr), 
      (SELECT 'Prod2' AS product,'d' AS gr), 
      (SELECT 'Prod3' AS product,'a' AS gr), 
      (SELECT 'Prod3' AS product,'b' AS gr), 
      (SELECT 'Prod3' AS product,'c' AS gr), 
      (SELECT 'Prod3' AS product,'d' AS gr)
  ) 
  GROUP BY product 
  HAVING NOT ',' + groups + ',' CONTAINS ',c,'
  AND ',' + groups + ',' CONTAINS ',a,'
  AND ',' + groups + ',' CONTAINS ',b,'
  AND ',' + groups + ',' CONTAINS ',d,'
)

Upvotes: 1

Related Questions