I'm working on a mock exam paper at the moment, however I have no set of correct answers and I'm not sure what the correct answer of this SQL query is.
Given a table:
foo, bar
a , 1
b , 3
a , 2
c , 1
and the query:
SELECT foo, sum(bar)
FROM table
GROUP BY foo
The two ways I can see this going are either:
a 3
a 3
b 3
c 1
or
a 3
b 3
c 1
Thanks.
It will be the last one.
GROUP BY will cause the set to group to the values of foo - a single line per each. The SUM aggregate function will sum the values of the bar columns.
This one:
SELECT foo, sum(bar)
FROM table
GROUP BY
foo
will give:
a 3
b 3
c 1
This one:
SELECT foo, SUM(bar) OVER (PARTITION BY foo)
FROM table
will give:
a 3
a 3
b 3
c 1
GROUP BY combines all rows that have the same value names in the GROUP BY. The SUM tell the query what to do with the values not named in the GROUP BY.
foo, bar
a , 1
b , 3
a , 2
c , 1
SELECT foo, sum(bar)
FROM table
GROUP BY foo
so you'll get 1 row for each foo: a, b and c, and it will SUM the bar values for each of those groups: a(1+2), b(1), c(1)
and result in your second result set:
a 3
b 3
c 1
Since this is homework, you need to learn:
FROM and JOIN determine & filter rows
WHERE more filters on the rows
GROUP BY combines those rows into groups
HAVING filters groups
ORDER BY arranges the remaining rows/groups
the group by will take effect first so the "computation" wil go like this
first group all rows on the value of foo. giving you
then produce a result row for each group based on the aggregate function sum. That is summing the value of bar for each group i.e.
The query:
select foo, sum(bar)
from table
group by foo
will give you the second set of output:
a 3
b 3
c 1
but not necessarily in that order. It may also give you those three lines in any other order. A group by clause does not mandate the order in which rows are returned.