Several levels of aggregation in Looker

[sanda_m]

Hi! 

I have a question I’ve been stuck with for a while and I am not sure how to implement it in LookML.

I need to count the number of unique sold items per company, and then sum all the counts and divide by the number of companies. So, in SQL query it would look something like this:

select sum(unique_sold_items)/count(company_counts)
      from (
SELECT
    company_id,
    COUNT(DISTINCT CASE WHEN (sold_or_not_flag = 'Sold') THEN company_id  ELSE NULL END) as unique_sold_items
    COUNT(DISTINCT(company_id)) as company_counts
FROM items
WHERE item_price = '10'
GROUP BY
    company_id
)

I manage to get for each company id the amount of sold items, and the company counts. However, how can I get the total sum of unique_sold_items per company divided by the company counts?