Results by date - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Results by date

Author

Topic

confuzed04
Starting Member

39 Posts

Posted - 2007-05-25 : 11:56:56

I have the following query and I am wanting to get the results by date. Please advise.
SELECT COUNT(UniqueCallID),
VoiceCallDetailRecord.[Type],
SUM(PaidBalCost),
SUM(BonusBalCost),
SUM(FreeMsgCost),
SUM(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM VoiceCallDetailRecord
WHERE VoiceCallDetailRecord.[Type] IN ('MOSMS','FreeMTSMS')
AND CallDate Between '01/01/2007' and '02/01/2007'
GROUP BY VoiceCallDetailRecord.[Type] and GROUP BY dateadd(day, datediff(day, 0, CallDate), 0)

UNION
SELECT COUNT(UniqueCallID),
ZeroChargeVCDRecord.[Type],
SUM(PaidBalCost),
SUM(BonusBalCost),
SUM(FreeMsgCost),
SUM(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes
FROM ZeroChargeVCDRecord
WHERE ZeroChargeVCDRecord.[Type] IN ('MOSMS','FreeMTSMS')
AND CallDate Between '01/01/2007' and '02/01/2007'
GROUP BY ZeroChargeVCDRecord.[Type]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-25 : 12:02:18

this looks rather similar to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=84117

Have you try to write your query in similar manner ?

confuzed04
Starting Member

39 Posts

Posted - 2007-05-25 : 12:24:06

Yeah. I tried doing the same thing to this one and it gave me an error. It doesn't like the two Group By statements. The 'and' bothers it for some reason.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-25 : 19:18:21

you have the syntax wrong. You cannot have 2 GROUP BY statement


GROUP BY VoiceCallDetailRecord.[Type] and GROUP BY, dateadd(day, datediff(day, 0, CallDate), 0)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-26 : 00:53:10

and Group by?

Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Madhivanan

Failing to plan is Planning to fail

confuzed04
Starting Member

39 Posts

Posted - 2007-05-29 : 14:48:14

Thank you so much khtan! You have been a big help! That worked. So you can never have two group by statements? Why can you not use and, yet you can with a WHERE statement? Just curious. Thanks again for all your help!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-05-29 : 23:50:12

you cant use and in group by as you use in Where clause
As I specified, learn sql

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources