TFD
ThoughtSpot For Developers•2mo ago
TomerLand

TomerLand - Hey. I have a problem with a query....

Hey. I have a problem with a query. I want to create an answer that shows some counts of an entity (that is group by 2 columns) and the total percentage of the total counts. spotter always tries to apply the total count on top of the grouping, and I can't figure out what how to add a total count in this query. in SQL is quite simple, for example 
WITH data AS (
  SELECT COUNT(*) AS cnt FROM `env0-data-warehouse`.`env0_dbt_dev`.`entities_environments`
)
SELECT 
  `ta_1`.`iac_version` AS `ca_1`, 
  `ta_2`.`type` AS `ca_2`, 
  COUNT(`ta_1`.`id`) AS `ca_3`, 
  d.cnt AS total_count
FROM `env0-data-warehouse`.`env0_dbt_dev`.`entities_environments` AS `ta_1`
  JOIN `env0-data-warehouse`.`env0_dbt_dev`.`entities_blueprints` AS `ta_2`
    ON `ta_1`.`blueprint_id` = `ta_2`.`id`,
  data d
WHERE NOT(`ta_1`.`iac_version` IS NULL)
GROUP BY 
  `ca_1`, 
  `ca_2`,
  d.cnt
WITH data AS (
  SELECT COUNT(*) AS cnt FROM `env0-data-warehouse`.`env0_dbt_dev`.`entities_environments`
)
SELECT 
  `ta_1`.`iac_version` AS `ca_1`, 
  `ta_2`.`type` AS `ca_2`, 
  COUNT(`ta_1`.`id`) AS `ca_3`, 
  d.cnt AS total_count
FROM `env0-data-warehouse`.`env0_dbt_dev`.`entities_environments` AS `ta_1`
  JOIN `env0-data-warehouse`.`env0_dbt_dev`.`entities_blueprints` AS `ta_2`
    ON `ta_1`.`blueprint_id` = `ta_2`.`id`,
  data d
WHERE NOT(`ta_1`.`iac_version` IS NULL)
GROUP BY 
  `ca_1`, 
  `ca_2`,
  d.cnt
the problem is that I can't figure out how to do that via your UI...
3 Replies
shikharTS
shikharTS•2mo ago
Can you ask this in you in app chat support or create a support case for this? Will get handled by the relevant team
TomerLand
TomerLandOP•2mo ago
the issue is resolve, I created a formula for that... it's just a shame that I couldn't do that via spotter 😦
shikharTS
shikharTS•2mo ago
Actually let me give this feedback to the spotter team

Did you find this page helpful?