CODEWITHSUNDEEP
google-bigquery
rbkk2016
rbkk2016

Reputation: 215

Sub select in bigquery with case statement

Below Query when I execute gives me a result as below (SELECT
CASE
WHEN prodName CONTAINS('AAA') THEN 'AAA'
WHEN prodName CONTAINS('BBB') THEN 'BBB'
WHEN prodName CONTAINS('CCC') THEN 'CCC'
WHEN prodName CONTAINS('DDD') THEN 'DDD'
WHEN prodName CONTAINS('EEE') THEN 'EEE'
WHEN prodName CONTAINS('FFF') THEN 'FFF'
WHEN prodName CONTAINS('GGG') THEN 'GGG'
WHEN prodName CONTAINS('HHH') THEN 'HHH'
ELSE 'Others'
END AS
PROD_NAME,
EXACT_COUNT_DISTINCT(user) AS Distinct_users
FROM [mytable]
GROUP BY 1
ORDER BY 2 DESC
IGNORE CASE)

enter image description here


Below query which is same as above but I have given a subselect in order to omit records if PROD_NAME = aaa returns a different result where it aggregates distinct users having value 1 to others and shows diff result.

SELECT PROD_NAME, EXACT_COUNT_DISTINCT(users) AS Distinct_users
FROM
(SELECT
CASE
WHEN prodName CONTAINS('AAA') THEN 'AAA'
WHEN prodName CONTAINS('BBB') THEN 'BBB'
WHEN prodName CONTAINS('CCC') THEN 'CCC'
WHEN prodName CONTAINS('DDD') THEN 'DDD'
WHEN prodName CONTAINS('EEE') THEN 'EEE'
WHEN prodName CONTAINS('FFF') THEN 'FFF'
WHEN prodName CONTAINS('GGG') THEN 'GGG'
WHEN prodName CONTAINS('HHH') THEN 'HHH'
ELSE 'Others'
END AS
PROD_NAME,
EXACT_COUNT_DISTINCT(user) AS Distinct_users
FROM [mytable]
GROUP BY 1
ORDER BY 2 DESC
IGNORE CASE)

enter image description here

Please let me know how to resolve the issue. I need the second query to provide same output as first but I do not need PROD_NAME = 'AAA'. I have included as case statement because my table contains several data such as 'aaa','AAA', 'aaa-in', 'Aaa' etc and I am grouping everything together to show the aggregate value

Upvotes: 0

Views: 1385

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173190

I need the second query to provide same output as first but I do not need PROD_NAME = 'AAA'

how about just simply adding HAVING clause as below 

SELECT 
  CASE 
    WHEN prodName CONTAINS('AAA') THEN 'AAA' 
    WHEN prodName CONTAINS('BBB') THEN 'BBB' 
    WHEN prodName CONTAINS('CCC') THEN 'CCC' 
    WHEN prodName CONTAINS('DDD') THEN 'DDD' 
    WHEN prodName CONTAINS('EEE') THEN 'EEE' 
    WHEN prodName CONTAINS('FFF') THEN 'FFF' 
    WHEN prodName CONTAINS('GGG') THEN 'GGG' 
    WHEN prodName CONTAINS('HHH') THEN 'HHH' 
    ELSE 'Others' 
  END AS 
  PROD_NAME, 
EXACT_COUNT_DISTINCT(user) AS Distinct_users 
FROM [mytable] 
GROUP BY 1 
HAVING PROD_NAME <> 'AAA'
ORDER BY 2 DESC 
IGNORE CASE

Upvotes: 1

Related Questions