Google Git
Sign in
apache/impala/refs/heads/asf-site/./docs/build/plain-html/topics/impala_sum.html
blob: b2abab1b9266253f86440562fca9b035b65265a3 [file]
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<meta name="copyright" content="(C) Copyright 2025" />
<meta name="DC.rights.owner" content="(C) Copyright 2025" />
<meta name="DC.Type" content="concept" />
<meta name="DC.Title" content="SUM Function" />
<meta name="DC.Relation" scheme="URI" content="../topics/impala_aggregate_functions.html" />
<meta name="prodname" content="Impala" />
<meta name="prodname" content="Impala" />
<meta name="version" content="Impala 3.4.x" />
<meta name="version" content="Impala 3.4.x" />
<meta name="DC.Format" content="XHTML" />
<meta name="DC.Identifier" content="sum" />
<link rel="stylesheet" type="text/css" href="../commonltr.css" />
<title>SUM Function</title>
</head>
<body id="sum">
<h1 class="title topictitle1" id="ariaid-title1">SUM Function</h1>
<div class="body conbody">
<p class="p">
An aggregate function that returns the sum of a set of numbers. Its single argument can be numeric column, or
the numeric result of a function or expression applied to the column value. Rows with a <code class="ph codeph">NULL</code>
value for the specified column are ignored. If the table is empty, or all the values supplied to
<code class="ph codeph">MIN</code> are <code class="ph codeph">NULL</code>, <code class="ph codeph">SUM</code> returns <code class="ph codeph">NULL</code>.
</p>
<p class="p">
<strong class="ph b">Syntax:</strong>
</p>
<pre class="pre codeblock"><code>SUM([DISTINCT | ALL] <var class="keyword varname">expression</var>) [OVER (<var class="keyword varname">analytic_clause</var>)]</code></pre>
<p class="p">
When the query contains a <code class="ph codeph">GROUP BY</code> clause, returns one value for each combination of
grouping values.
</p>
<p class="p">
<strong class="ph b">Return type:</strong> <code class="ph codeph">BIGINT</code> for integer arguments, <code class="ph codeph">DOUBLE</code> for floating-point
arguments
</p>
<p class="p">
<strong class="ph b">Complex type considerations:</strong>
</p>
<p class="p">
To access a column with a complex type (<code class="ph codeph">ARRAY</code>, <code class="ph codeph">STRUCT</code>,
or <code class="ph codeph">MAP</code>) in an aggregation function, you unpack the individual elements
using join notation in the query, and then apply the function to the final scalar item,
field, key, or value at the bottom of any nested type hierarchy in the column. See
<a class="xref" href="../shared/../topics/impala_complex_types.html#complex_types">Complex Types (Impala 2.3 or higher only)</a> for details about using
complex types in Impala.
</p>
<div class="p">
The following example demonstrates calls to several aggregation functions using values
from a column containing nested complex types (an <code class="ph codeph">ARRAY</code> of
<code class="ph codeph">STRUCT</code> items). The array is unpacked inside the query using join
notation. The array elements are referenced using the <code class="ph codeph">ITEM</code>
pseudocolumn, and the structure fields inside the array elements are referenced using
dot notation. Numeric values such as <code class="ph codeph">SUM()</code> and <code class="ph codeph">AVG()</code>
are computed using the numeric <code class="ph codeph">R_NATIONKEY</code> field, and the
general-purpose <code class="ph codeph">MAX()</code> and <code class="ph codeph">MIN()</code> values are computed
from the string <code class="ph codeph">N_NAME</code> field.
<pre class="pre codeblock"><code>describe region;
+-------------+-------------------------+---------+
| name | type | comment |
+-------------+-------------------------+---------+
| r_regionkey | smallint | |
| r_name | string | |
| r_comment | string | |
| r_nations | array&lt;struct&lt; | |
| | n_nationkey:smallint, | |
| | n_name:string, | |
| | n_comment:string | |
| | &gt;&gt; | |
+-------------+-------------------------+---------+
select r_name, r_nations.item.n_nationkey
from region, region.r_nations as r_nations
order by r_name, r_nations.item.n_nationkey;
+-------------+------------------+
| r_name | item.n_nationkey |
+-------------+------------------+
| AFRICA | 0 |
| AFRICA | 5 |
| AFRICA | 14 |
| AFRICA | 15 |
| AFRICA | 16 |
| AMERICA | 1 |
| AMERICA | 2 |
| AMERICA | 3 |
| AMERICA | 17 |
| AMERICA | 24 |
| ASIA | 8 |
| ASIA | 9 |
| ASIA | 12 |
| ASIA | 18 |
| ASIA | 21 |
| EUROPE | 6 |
| EUROPE | 7 |
| EUROPE | 19 |
| EUROPE | 22 |
| EUROPE | 23 |
| MIDDLE EAST | 4 |
| MIDDLE EAST | 10 |
| MIDDLE EAST | 11 |
| MIDDLE EAST | 13 |
| MIDDLE EAST | 20 |
+-------------+------------------+
select
r_name,
count(r_nations.item.n_nationkey) as count,
sum(r_nations.item.n_nationkey) as sum,
avg(r_nations.item.n_nationkey) as avg,
min(r_nations.item.n_name) as minimum,
max(r_nations.item.n_name) as maximum,
ndv(r_nations.item.n_nationkey) as distinct_vals
from
region, region.r_nations as r_nations
group by r_name
order by r_name;
+-------------+-------+-----+------+-----------+----------------+---------------+
| r_name | count | sum | avg | minimum | maximum | distinct_vals |
+-------------+-------+-----+------+-----------+----------------+---------------+
| AFRICA | 5 | 50 | 10 | ALGERIA | MOZAMBIQUE | 5 |
| AMERICA | 5 | 47 | 9.4 | ARGENTINA | UNITED STATES | 5 |
| ASIA | 5 | 68 | 13.6 | CHINA | VIETNAM | 5 |
| EUROPE | 5 | 77 | 15.4 | FRANCE | UNITED KINGDOM | 5 |
| MIDDLE EAST | 5 | 58 | 11.6 | EGYPT | SAUDI ARABIA | 5 |
+-------------+-------+-----+------+-----------+----------------+---------------+
</code></pre>
</div>
<p class="p">
<strong class="ph b">Examples:</strong>
</p>
<p class="p">
The following example shows how to use <code class="ph codeph">SUM()</code> to compute the total for all the values in the
table, a subset of values, or the sum for each combination of values in the <code class="ph codeph">GROUP BY</code> clause:
</p>
<pre class="pre codeblock"><code>-- Total all the values for this column in the table.
select sum(c1) from t1;
-- Find the total for this column from a subset of the table.
select sum(c1) from t1 where month = 'January' and year = '2013';
-- Find the total from a set of numeric function results.
select sum(length(s)) from t1;
-- Often used with functions that return predefined values to compute a score.
select sum(case when grade = 'A' then 1.0 when grade = 'B' then 0.75 else 0) as class_honors from test_scores;
-- Can also be used in combination with DISTINCT and/or GROUP BY.
-- Return more than one result.
select month, year, sum(purchase_price) from store_stats group by month, year;
-- Filter the input to eliminate duplicates before performing the calculation.
select sum(distinct x) from t1;
</code></pre>
<div class="p">
The following examples show how to use <code class="ph codeph">SUM()</code> in an analytic context. They use a table
containing integers from 1 to 10. Notice how the <code class="ph codeph">SUM()</code> is reported for each input value, as
opposed to the <code class="ph codeph">GROUP BY</code> clause which condenses the result set.
<pre class="pre codeblock"><code>select x, property, sum(x) <strong class="ph b">over (partition by property)</strong> as sum from int_t where property in ('odd','even');
+----+----------+-----+
| x | property | sum |
+----+----------+-----+
| 2 | even | 30 |
| 4 | even | 30 |
| 6 | even | 30 |
| 8 | even | 30 |
| 10 | even | 30 |
| 1 | odd | 25 |
| 3 | odd | 25 |
| 5 | odd | 25 |
| 7 | odd | 25 |
| 9 | odd | 25 |
+----+----------+-----+
</code></pre>
Adding an <code class="ph codeph">ORDER BY</code> clause lets you experiment with results that are cumulative or apply to a moving
set of rows (the <span class="q">"window"</span>). The following examples use <code class="ph codeph">SUM()</code> in an analytic context
(that is, with an <code class="ph codeph">OVER()</code> clause) to produce a running total of all the even values,
then a running total of all the odd values. The basic <code class="ph codeph">ORDER BY x</code> clause implicitly
activates a window clause of <code class="ph codeph">RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</code>,
which is effectively the same as <code class="ph codeph">ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW</code>,
therefore all of these examples produce the same results:
<pre class="pre codeblock"><code>select x, property,
sum(x) over (partition by property <strong class="ph b">order by x</strong>) as 'cumulative total'
from int_t where property in ('odd','even');
+----+----------+------------------+
| x | property | cumulative total |
+----+----------+------------------+
| 2 | even | 2 |
| 4 | even | 6 |
| 6 | even | 12 |
| 8 | even | 20 |
| 10 | even | 30 |
| 1 | odd | 1 |
| 3 | odd | 4 |
| 5 | odd | 9 |
| 7 | odd | 16 |
| 9 | odd | 25 |
+----+----------+------------------+
select x, property,
sum(x) over
(
partition by property
<strong class="ph b">order by x</strong>
<strong class="ph b">range between unbounded preceding and current row</strong>
) as 'cumulative total'
from int_t where property in ('odd','even');
+----+----------+------------------+
| x | property | cumulative total |
+----+----------+------------------+
| 2 | even | 2 |
| 4 | even | 6 |
| 6 | even | 12 |
| 8 | even | 20 |
| 10 | even | 30 |
| 1 | odd | 1 |
| 3 | odd | 4 |
| 5 | odd | 9 |
| 7 | odd | 16 |
| 9 | odd | 25 |
+----+----------+------------------+
select x, property,
sum(x) over
(
partition by property
<strong class="ph b">order by x</strong>
<strong class="ph b">rows between unbounded preceding and current row</strong>
) as 'cumulative total'
from int_t where property in ('odd','even');
+----+----------+------------------+
| x | property | cumulative total |
+----+----------+------------------+
| 2 | even | 2 |
| 4 | even | 6 |
| 6 | even | 12 |
| 8 | even | 20 |
| 10 | even | 30 |
| 1 | odd | 1 |
| 3 | odd | 4 |
| 5 | odd | 9 |
| 7 | odd | 16 |
| 9 | odd | 25 |
+----+----------+------------------+
</code></pre>
Changing the direction of the <code class="ph codeph">ORDER BY</code> clause causes the intermediate
results of the cumulative total to be calculated in a different order:
<pre class="pre codeblock"><code>select sum(x) over (partition by property <strong class="ph b">order by x desc</strong>) as 'cumulative total'
from int_t where property in ('odd','even');
+----+----------+------------------+
| x | property | cumulative total |
+----+----------+------------------+
| 10 | even | 10 |
| 8 | even | 18 |
| 6 | even | 24 |
| 4 | even | 28 |
| 2 | even | 30 |
| 9 | odd | 9 |
| 7 | odd | 16 |
| 5 | odd | 21 |
| 3 | odd | 24 |
| 1 | odd | 25 |
+----+----------+------------------+
</code></pre>
The following examples show how to construct a moving window, with a running total taking into account 1 row before
and 1 row after the current row, within the same partition (all the even values or all the odd values).
Because of a restriction in the Impala <code class="ph codeph">RANGE</code> syntax, this type of
moving window is possible with the <code class="ph codeph">ROWS BETWEEN</code> clause but not the <code class="ph codeph">RANGE BETWEEN</code>
clause:
<pre class="pre codeblock"><code>select x, property,
sum(x) over
(
partition by property
<strong class="ph b">order by x</strong>
<strong class="ph b">rows between 1 preceding and 1 following</strong>
) as 'moving total'
from int_t where property in ('odd','even');
+----+----------+--------------+
| x | property | moving total |
+----+----------+--------------+
| 2 | even | 6 |
| 4 | even | 12 |
| 6 | even | 18 |
| 8 | even | 24 |
| 10 | even | 18 |
| 1 | odd | 4 |
| 3 | odd | 9 |
| 5 | odd | 15 |
| 7 | odd | 21 |
| 9 | odd | 16 |
+----+----------+--------------+
-- Doesn't work because of syntax restriction on RANGE clause.
select x, property,
sum(x) over
(
partition by property
<strong class="ph b">order by x</strong>
<strong class="ph b">range between 1 preceding and 1 following</strong>
) as 'moving total'
from int_t where property in ('odd','even');
ERROR: AnalysisException: RANGE is only supported with both the lower and upper bounds UNBOUNDED or one UNBOUNDED and the other CURRENT ROW.
</code></pre>
</div>
<p class="p">
<strong class="ph b">Restrictions:</strong>
</p>
<p class="p">
Due to the way arithmetic on <code class="ph codeph">FLOAT</code> and <code class="ph codeph">DOUBLE</code> columns
uses high-performance hardware instructions, and distributed queries can perform these
operations in different order for each query, results can vary slightly for aggregate
function calls such as <code class="ph codeph">SUM()</code> and <code class="ph codeph">AVG()</code> for
<code class="ph codeph">FLOAT</code> and <code class="ph codeph">DOUBLE</code> columns, particularly on large data
sets where millions or billions of values are summed or averaged. For perfect
consistency and repeatability, use the <code class="ph codeph">DECIMAL</code> data type for such
operations instead of <code class="ph codeph">FLOAT</code> or <code class="ph codeph">DOUBLE</code>.
</p>
<p class="p">
<strong class="ph b">Related information:</strong>
</p>
<p class="p">
<a class="xref" href="impala_analytic_functions.html#analytic_functions">Impala Analytic Functions</a>
</p>
</div>
<div class="related-links">
<div class="familylinks">
<div class="parentlink"><strong>Parent topic:</strong> <a class="link" href="../topics/impala_aggregate_functions.html">Impala Aggregate Functions</a></div>
</div>
</div></body>
</html>