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?

[leobardor]

Hi Sanda. 

One approach we can use is to implement this guide :

Looker- From measure to dimension

Basically, you will need to place a derived table first  and then use this output result fields as dimensions. 

Hope this helps! 

Regards

Leo