How to add a new column to this bigquery/sql query using a subquery or other method-CodePudding

I've created this query:

SELECT
  country,
  place,
  label_date,
  SUM(CASE
      WHEN Category = 'cat1' THEN Value
    ELSE
    0
  END
    ) AS t_s,
  SUM(CASE
      WHEN Category = 'cat2' THEN Value
    ELSE
    0
  END
    ) AS non_ps,
  SUM(CASE
      WHEN Category = 'cat3' THEN Value
    ELSE
    0
  END
    ) AS f_h,
  SUM(CASE
      WHEN Category = 'cat4' THEN Value
    ELSE
    0
  END) AS f_s,
  SUM(CASE
      WHEN Category = 'cat5' THEN Value
    ELSE
    0
  END) AS o_hp,
  SUM(CASE
      WHEN Category = 'cat6' THEN Value
    ELSE
    0
  END) AS o_hps,
  SUM(CASE
      WHEN Category = 'cat7' THEN Value
    ELSE
    0
  END) AS a_s,
  SUM(CASE
      WHEN Category = 'cat8' THEN Value
    ELSE
    0
  END) AS u_s
FROM
  `my_database`
where country is not null group by country, place, label_date order by country desc

I used aggregations because I need to avoid having nulls and zeroes. like this and not like this.

Now I have this other query:

 select country, place, label_date, if((sum(value)/count(Value))=0,1,sum(value)/count(Value)) as s_u from `my_database` where Category = 'cat9' group by country, place, label_date

and I want to add it but I can't.

I've tried doing this join:

FROM
  `my_database` as main
JOIN(select country, place, label_date, if((sum(value)/count(Value))=0,1,sum(value)/count(Value)) as s_u from `my_database` where Category = 'cat9' group by country, place, label_date) as s_u ON main.country = s_u.country
where country is not null group by country, place, label_date order by country desc

I used country since sadly I don't have a unique id, but I get the error country is redundant.

I also tried this:

SUM(CASE
      WHEN Category = 'cat8' THEN Value
    ELSE
    0
  END) AS u_s,
SUM(CASE
      WHEN Category = 'cat9' THEN if((sum(value)/count(Value))=0,1,sum(value)/count(Value))
    ELSE
    0
  END) AS u_s
FROM
  `my_database`
where country is not null group by country, place, label_date order by country desc

but now I get a can aggregate aggregations error.

edited to add sample data:

I need the data in this way and not like this were more than one row with country-place shows.

If I add one more case without an aggregation bigquery will show this: select list expression references which is neither grouped nor aggregated

CodePudding user response：

I'm rewriting my answer here, based on your feedback and data.

I think the approach you may need to take here is to split up the aggregations and do a few calculations for the records where Category = 'cat9' and non-null country values.

We'll compute the COUNT() of those records, grouped by country, place, and label_date like your first query, as well as the SUM(value). Then we'll place your original first query in another aggregated CTE, then we'll select and join them together at the end, along with adding the conditional logic for calculating s_u

WITH Cat9s AS
(
    SELECT 
        country
        , place
        , label_date
        , COUNT(*) AS TotalCat9s
        , SUM(IFNULL(value,0)) AS Cat9Sum
    FROM `my_database` 
    WHERE Category = 'cat9' 
    AND country IS NOT NULL
    GROUP BY country
        , place
        , label_date
)
,Aggregations AS
(
    SELECT
      country,
      place,
      label_date,
      SUM(CASE
          WHEN Category = 'cat1' THEN Value
        ELSE
        0
      END
        ) AS t_s,
      SUM(CASE
          WHEN Category = 'cat2' THEN Value
        ELSE
        0
      END
        ) AS non_ps,
      SUM(CASE
          WHEN Category = 'cat3' THEN Value
        ELSE
        0
      END
        ) AS f_h,
      SUM(CASE
          WHEN Category = 'cat4' THEN Value
        ELSE
        0
      END) AS f_s,
      SUM(CASE
          WHEN Category = 'cat5' THEN Value
        ELSE
        0
      END) AS o_hp,
      SUM(CASE
          WHEN Category = 'cat6' THEN Value
        ELSE
        0
      END) AS o_hps,
      SUM(CASE
          WHEN Category = 'cat7' THEN Value
        ELSE
        0
      END) AS a_s,
      SUM(CASE
          WHEN Category = 'cat8' THEN Value
        ELSE
        0
      END) AS u_s
    FROM
      `my_database`
    where country is not null 
    GROUP by country, place, label_date
)
SELECT 
    a.country
    ,a.place
    ,a.label_date
    ,a.t_s
    ,a.non_ps
    ,a.f_h
    ,a.f_s
    ,a.o_hp
    ,a.o_hps
    ,a.a_s
    ,a.u_s
    ,CASE WHEN c.Cat9Sum = 0 THEN 1 
            WHEN c.Cat9Sum <> 0 THEN c.Cat9Sum / c.TotalCat9s
        ELSE NULL END AS s_u
FROM Aggregations a
LEFT JOIN Cat9s c ON a.country = c.country 
        AND a.place = c.place
        AND a.label_date = c.label_date