Try It Yourself - SyntaxStudy Editor

HTML / CSS / JS

-- Basic aggregates
SELECT
  category_id,
  COUNT(*)                             AS total_products,
  COUNT(*) FILTER (WHERE is_active)    AS active_products,
  SUM(stock)                           AS total_stock,
  ROUND(AVG(price)::NUMERIC, 2)        AS avg_price,
  MIN(price)                           AS min_price,
  MAX(price)                           AS max_price,
  STRING_AGG(name, ', ' ORDER BY name) AS product_names
FROM products
GROUP BY category_id
HAVING COUNT(*) > 2
ORDER BY total_products DESC;

-- ROLLUP — subtotals and grand total
SELECT
  category_id,
  status,
  SUM(total)  AS revenue,
  COUNT(*)    AS orders
FROM orders
GROUP BY ROLLUP (category_id, status);
-- Generates: (category, status), (category), ()

-- ARRAY_AGG — collect values into an array
SELECT user_id, ARRAY_AGG(product_id ORDER BY added_at) AS wishlist
FROM wishlists
GROUP BY user_id;

-- JSON_AGG — aggregate rows into a JSON array
SELECT
  u.id,
  u.name,
  JSON_AGG(JSON_BUILD_OBJECT('id', o.id, 'total', o.total)) AS orders
FROM users u
JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;