3 replies

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...

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

Similar Threads