What is the result of this SQL query?

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 th开发者_运维技巧is 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.

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

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

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

• group1: a,1 a,2
• group2: b,3
• group3: c,1

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.

• a,3
• b,3
• c,1

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.