CODEWITHSUNDEEP
google-bigquery
wizkids121
wizkids121

Reputation: 656

Constructing A Query In BigQuery With CASE Statements

So I'm trying to construct a query in BigQuery that I'm struggling with for a final part.

As of now I have:

SELECT
UNIQUE(Name) as SubscriptionName,
ID,
Interval,
COUNT(mantaSubscriptionIdmetadata) AS SubsPurchased,
SUM(RevenueGenerated) as RevenueGenerated
FROM (
SELECT
mantaSubscriptionIdmetadata,
planIdmetadata,
INTEGER(Amount) as RevenueGenerated
FROM
[sample_internal_data.charge0209]
WHERE
revenueSourcemetadata = 'new'
AND
Status = 'Paid'
GROUP BY
mantaSubscriptionIdmetadata,
planIdmetadata,
RevenueGenerated
 )a
JOIN (
SELECT
id,
Name,
Interval
FROM
[sample_internal_data.subplans]
WHERE
id in ('150017','150030','150033','150019')
GROUP BY
id,
Name,
Interval )b
ON
a.planIdmetadata = b.id
GROUP BY
ID,
Interval,
Name
ORDER BY
Interval ASC

The resulting query looks like this Which is exactly what I'm looking for up to that point.

Now what I'm stuck on this. There is another column I need to add called SalesRepName. The resulting field will either be null or not null. If its null it means it was sold online. If its not null, it means it was sold via telephone. What I want to do is create two additional columns where it says how many were sold via telesales and via online. The sum total of the two columns will always equal the SubsPurchased total.

Can anyone help?

Upvotes: 1

Views: 3544

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

Try below:
it assumes your SalesRepName field is in [sample_internal_data.charge0209] table
and then it uses "tiny version" of SUM(CASE ... WHEN ...) which works when you need 0 or 1 as a result to be SUM'ed

SUM(SalesRepName IS NULL) AS onlinesales,
SUM(NOT SalesRepName IS NULL) AS telsales

SELECT
  UNIQUE(Name) AS SubscriptionName,
  ID,
  Interval,
  COUNT(mantaSubscriptionIdmetadata) AS SubsPurchased,
  SUM(RevenueGenerated) AS RevenueGenerated, 
  SUM(SalesRepName IS NULL) AS onlinesales,
  SUM(NOT SalesRepName IS NULL) AS telesales
FROM (
  SELECT SalesRepName, mantaSubscriptionIdmetadata, planIdmetadata, INTEGER(Amount) AS RevenueGenerated
  FROM [sample_internal_data.charge0209]
  WHERE revenueSourcemetadata = 'new'
    AND Status = 'Paid'
  GROUP BY mantaSubscriptionIdmetadata, planIdmetadata, RevenueGenerated
 )a
JOIN (
  SELECT id, Name, Interval
  FROM [sample_internal_data.subplans]
  WHERE id IN ('150017','150030','150033','150019')
  GROUP BY id, Name, Interval
)b
ON a.planIdmetadata = b.id
GROUP BY ID, Interval, Name
ORDER BY Interval ASC

Upvotes: 0

oulenz
oulenz

Reputation: 1244

You can include case statements within aggregate functions. Here you could choose sum(case when SalesRepName is null then 1 else 0 end) as online and sum(case when SalesRepName is not null then 1 else 0 end) as telesales.

count(case when SalesRepName is null then 1 end) as online would give the same result. Using sum in these situations is simply my personal preference.

Note that omitting the else clause is equivalent to setting else null, and null isn't counted by count. This can be very useful in combination with exact_count_distinct, which has no equivalent in terms of sum.

Upvotes: 1

Related Questions