Help with Calculated Field in GCP Query

[Borghese]

Hello everyone, I hope I'm posting in the right section. I'm new to the world of GCP, and I'm working with a SQL query directly in the GCP console. I need to understand how to create a calculated field, which gives me a distinct count of the contract code field, while maintaining the link with the related lot. I usually worked with Tableau, and I would have written this function as follows: SUM({ INCLUDE [Lot] : COUNTD([Contract Code])}) How can I write the same formula in the Google console? (obviously, I expect a new field to be created, so I imagine I need to end with an AS "XXX" to name it). Thanks in advance to everyone.

[ms4446 · Accepted solution]

In BigQuery, you can achieve the same result as the Tableau formula SUM({ INCLUDE [Lot] : COUNTD([Contract Code])}) by using a combination of COUNT and DISTINCT along with a GROUP BY clause.

Here is how you can write the equivalent SQL query in BigQuery:

SELECT 
  Lot,
  COUNT(DISTINCT Contract_Code) AS Distinct_Contract_Count
FROM 
  your_table_name
GROUP BY 
  Lot

In this query:

• Lot is the field you want to include.
• Contract_Code is the field for which you want to count the distinct values.
• COUNT(DISTINCT Contract_Code) gives you the distinct count of Contract_Code for each Lot.
• The GROUP BY Lot clause ensures that the count is grouped by each Lot.

If you need to further aggregate these results, for example, to get the sum of distinct counts across multiple lots, you can use a subquery:

SELECT 
  SUM(Distinct_Contract_Count) AS Total_Distinct_Contract_Count
FROM (
  SELECT 
    Lot,
    COUNT(DISTINCT Contract_Code) AS Distinct_Contract_Count
  FROM 
    your_table_name
  GROUP BY 
    Lot
)

This will give you the total distinct contract count across all lots.

[Borghese]

Thank you so much, your solution worked