GROUP BY question

Hi,
I have a table which includes date (day, month, year, as one col.) &
deposits for each day. I need to summarize the deposits into totals per
month. I have used SUM(deposit). I need to group it by month/year. How do I
do this? If I use DATENAME to create a col. to return a col. which includes
only the date & year, how can I use this to group the aggregate function? I
can��t use its alias in the group by clause & I can't group by the date in
its original format as this won't return monthly groupings. Is it possible t
o
do this without creating separate columns for each element of the date? Belo
w
is the query:
SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
SUM(deposit)
FROM loan_tbl
GROUP BY what? (I'd like to group by month - Year)
Any input would be greatly appreciated
thanks, AntHave the same expression in your GROUP BY as in the column list, or use a de
rived table, such as:
SELECT [mnth & yr], SUM(deposit)
FROM
(
SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
deposit
FROM loan_tbl
) AS d
GROUP BY [mnth & yr]
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:9D493D95-C622-4BB2-94DC-A389D823FF57@.microsoft.com...
> Hi,
> I have a table which includes date (day, month, year, as one col.) &
> deposits for each day. I need to summarize the deposits into totals per
> month. I have used SUM(deposit). I need to group it by month/year. How do
I
> do this? If I use DATENAME to create a col. to return a col. which include
s
> only the date & year, how can I use this to group the aggregate function?
I
> can't use its alias in the group by clause & I can't group by the date in
> its original format as this won't return monthly groupings. Is it possible
to
> do this without creating separate columns for each element of the date? Be
low
> is the query:
> SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
> SUM(deposit)
> FROM loan_tbl
> GROUP BY what? (I'd like to group by month - Year)
> Any input would be greatly appreciated
> thanks, Ant|||Ant
SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
SUM(deposit)
FROM loan_tbl
GROUP BY DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date)
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:9D493D95-C622-4BB2-94DC-A389D823FF57@.microsoft.com...
> Hi,
> I have a table which includes date (day, month, year, as one col.) &
> deposits for each day. I need to summarize the deposits into totals per
> month. I have used SUM(deposit). I need to group it by month/year. How do
I
> do this? If I use DATENAME to create a col. to return a col. which
includes
> only the date & year, how can I use this to group the aggregate function?
I
> cant use its alias in the group by clause & I can't group by the date in
> its original format as this won't return monthly groupings. Is it possible
to
> do this without creating separate columns for each element of the date?
Below
> is the query:
> SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
> SUM(deposit)
> FROM loan_tbl
> GROUP BY what? (I'd like to group by month - Year)
> Any input would be greatly appreciated
> thanks, Ant|||That's great. Thank you very much. Both solutions work nicely.
Regards
Ant
"Tibor Karaszi" wrote:

> Have the same expression in your GROUP BY as in the column list, or use a
derived table, such as:
> SELECT [mnth & yr], SUM(deposit)
> FROM
> (
> SELECT DATENAME(MONTH,date)+ ' ' + DATENAME(YEAR,date) as [mnth & yr],
> deposit
> FROM loan_tbl
> ) AS d
> GROUP BY [mnth & yr]
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> http://www.sqlug.se/
>
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:9D493D95-C622-4BB2-94DC-A389D823FF57@.microsoft.com...
>
>