Skip to content
The Chat Archive
sonar-reasoning-pro-high LMArena Search Arena 24K

How to use sum_if like function in Presto?

  1. User

    How to use sum_if like function in Presto?

  2. Assistant

    In Presto, while there's no direct SUM_IF function, you can achieve conditional summation using standard SQL patterns or Presto-specific syntax. Here are the primary methods:


    1. Using SUM with CASE

    This is the most portable approach across SQL dialects. It filters values before summation using a CASE expression inside SUM:

    SELECT
      SUM(CASE WHEN condition THEN value_column ELSE 0 END) AS total
    FROM table
    

    Example: Sum sales where category = 'Electronics':

    SELECT
      SUM(CASE WHEN category = 'Electronics' THEN sales ELSE 0 END) AS electronics_sales
    FROM sales_data
    

    [6][7]


    2. Using FILTER Clause (Presto-Specific)

    Presto supports the FILTER clause for cleaner syntax when applying conditions to aggregates:

    SELECT
      SUM(value_column) FILTER (WHERE condition) AS total
    FROM table
    

    Example: Sum revenue for region = 'Europe':

    SELECT
      SUM(revenue) FILTER (WHERE region = 'Europe') AS europe_revenue
    FROM orders
    

    [8]


    Key Notes:

    • Performance: Both methods are optimized in Presto, but FILTER can be more readable for multiple conditions[2][8].
    • Multiple Conditions: Combine with AND/OR:
      SUM(CASE WHEN (condition1 OR condition2) THEN value END)
      -- OR
      SUM(value) FILTER (WHERE condition1 AND condition2)
      
    • Grouped Aggregations: Add GROUP BY for subtotals:
      SELECT
        category,
        SUM(sales) FILTER (WHERE year = 2024) AS sales_2024
      FROM products
      GROUP BY category
      

    Comparison with Other Tools

    • Excel: =SUMIF(A:A, ">100", B:B) translates to SUM(CASE WHEN A > 100 THEN B END)[6].
    • Metabase/Spreadsheets: Similar CASE-based logic applies[3][6].

    For counting instead of summing, use COUNT_IF(condition)[2][8].

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.

Identical first prompt