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 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, AntHave 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...
> 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 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|||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
> 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?
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...
> > 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 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
>
>