vhpcomp
asked on
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),d ns_log_dat e) 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!
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),d
group by dns_date, order by dns_date
Apparently I can't specify a table alias (dns_date) in the group by clause
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>> 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 :-) .
Except in the ORDER BY, where it is allowed :-) .
ASKER
Thanks everyone!
rafrancisco and Chaosian, your answer times were so close, I felt it was only fair to split down the middle.
rafrancisco and Chaosian, your answer times were so close, I felt it was only fair to split down the middle.
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.