Problem
5
:
Grouped
Sales
by
Month
Question
:
Assuming
a
table
sales
(
order_id
INT
,
amount
DECIMAL
,
sale_date
DATE
)
,
calculate
total
sales
for
each
month
.
Solution
:
____________________
____________________
(
____________________
____________________
____________________
)
____________________
____________________
,
____________________
(
____________________
____________________
____________________
)
____________________
____________________
,
____________________
(
____________________
)
____________________
____________________
____________________
____________________
____________________
____________________
____________________
(
____________________
____________________
____________________
)
,
____________________
(
____________________
____________________
____________________
)
____________________
____________________
____________________
,
____________________
;
SELECT
EXTRACT
(
YEAR
FROM
sale_date
)
AS
sale_year
,
EXTRACT
(
MONTH
FROM
sale_date
)
AS
sale_month
,
SUM
(
amount
)
AS
total_sales
FROM
sales
GROUP
BY
EXTRACT
(
YEAR
FROM
sale_date
)
,
EXTRACT
(
MONTH
FROM
sale_date
)
ORDER
BY
sale_year
,
sale_month
;
Explanation
:
The
SUM
(
)
function
is
used
to
aggregate
sales
per
month
,
grouped
by
year
and
month
extracted
from
sale_date
.
The
GROUP
BY
clause
groups
the
data
into
sets
that
share
the
same
year
and
month
so
that
SUM
(
)
can
compute
totals
for
each
group
.