How to insert query filter based on count result in redash

Question

it is a simple question I believe, but as I'm not familiar with redash it seems hard.

I have a query in redash,

SELECT si.variant_id,
       v.sku,
       COUNT(CASE
                 WHEN a.viewable_type = 'Spree::Variant' THEN a.id
             END) AS photo
FROM spree_stock_items si
LEFT JOIN spree_stock_locations sl ON sl.id = si.stock_location_id
LEFT JOIN spree_assets a ON si.variant_id = a.viewable_id
LEFT JOIN spree_variants v ON v.id = si.variant_id
WHERE-- viewable_type = 'Spree::Variant'
sl.name='{{store_location}}'
  AND si.deleted_at IS NULL
  AND sl.country_id = '2'
GROUP BY 1,
         2
ORDER BY 1
LIMIT 200000

which give results like this:

as of now, I can filter the results on the store location. but now I want to add query filter based on the column photo. How can I do it? basically I want to filter the result in redash dashboard by the store location and the count in photo column.

Answer 1

You can use a having clause - but it requires repeating the aggregate expression. For example:

SELECT si.variant_id,
       v.sku,
       COUNT(a.id) FILTER(WHERE a.viewable_type = 'Spree::Variant') AS photo
FROM spree_stock_items si
LEFT JOIN spree_stock_locations sl ON sl.id = si.stock_location_id
LEFT JOIN spree_assets a ON si.variant_id = a.viewable_id
LEFT JOIN spree_variants v ON v.id = si.variant_id
WHERE-- viewable_type = 'Spree::Variant'
    sl.name='{{store_location}}'
    AND si.deleted_at IS NULL
    AND sl.country_id = '2'
GROUP BY 1, 2
HAVING COUNT(a.id) FILTER(WHERE a.viewable_type = 'Spree::Variant') > 1
ORDER BY 1
LIMIT 200000

Note that I rewrote the count() to use the standard filter clause, which makes the syntax neater.

Alternatively, you can use a subquery, and a WHERE clause in the outer query:

SELECT *
FROM (
    SELECT si.variant_id,
           v.sku,
           COUNT(a.id) FILTER(WHERE a.viewable_type = 'Spree::Variant') AS photo
    FROM spree_stock_items si
    LEFT JOIN spree_stock_locations sl ON sl.id = si.stock_location_id
    LEFT JOIN spree_assets a ON si.variant_id = a.viewable_id
    LEFT JOIN spree_variants v ON v.id = si.variant_id
    WHERE-- viewable_type = 'Spree::Variant'
    sl.name='{{store_location}}'
      AND si.deleted_at IS NULL
      AND sl.country_id = '2'
    GROUP BY 1, 2
) t
WHERE photo > 1
ORDER BY 1
LIMIT 200000