T-SQL group by to return daily total

This should be simple, I know.  I need a query to return a total by day of field  dn_calls where dn_num equals a certain number, for example 1:


sample table data:

dns_log_date                 dns_num                 dns_calls
6/15/2005 12:00am                      1                    20
6/15/2005 12:30am                      1                    25
6/16/2005 12:00am                      1                    15
6/16/2005 12:00am                      1                     18

My query should return something like

dns_date       dns_daily_calls    dns_num
6/15/2005    45                       1
6/16/2005    33                       1


What I've tried without success is:
select cast(convert(varchar(11),dns_log_date) as datetime) as 'dns_date', sum(dns_calls_offered) as 'dns_daily_calls', dns_num from mytable where dns_num = 1
group by dns_date, order by dns_date

Apparently I can't specify a table alias (dns_date) in the group by clause

Thanks!
LVL 1
vhpcompAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rafranciscoCommented:
select cast(convert(varchar(10),dns_log_date, 101) as datetime) as 'dns_date',
       sum(dns_calls_offered) as 'dns_daily_calls', dns_num
from mytable
where dns_num = 1
group by cast(convert(varchar(10),dns_log_date, 101) as datetime), dns_num
order by cast(convert(varchar(10),dns_log_date, 101) as datetime)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeff CertainCommented:
select convert(datetime,dns_log_date,101) as 'dns_date', sum(dns_calls_offered) as 'dns_daily_calls', dns_num from mytable
where dns_num = 1
group by convert(datetime,dns_log_date,101)
order by convert(datetime,dns_log_date,101)
rafranciscoCommented:
>> Apparently I can't specify a table alias (dns_date) in the group by clause <<

Yes, you cannot use a column alias (not table alias) in the group by clause or in any other part of your SELECT statement.  You have to repeat the statement that made up that alias.
Scott PletcherSenior DBACommented:
>> Yes, you cannot use a column alias (not table alias) in the group by clause or in any other part of your SELECT statement. <<

Except in the ORDER BY, where it is allowed :-) .
vhpcompAuthor Commented:
Thanks everyone!
rafrancisco and Chaosian, your answer times were so close, I felt it was only fair to split down the middle.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.