Select Query - 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

Select Query

Author

Topic

lemondash
Posting Yak Master

159 Posts

Posted - 2007-06-01 : 06:00:33

Morning,

I have a query that i need to right. This is what i'm trying to do.
Have a table called tbldocuments and the two columns that i'm interested in are dtissuedate and ldocumentid, and ldocumentid is a primary key which is unqiue. am trying to do a group by on the whole table to tell how many are loaded by month and year(not day). example of my result sshould look like this..

21 2007 01
34 2007 02

This is what i have wrote so far but not working, any ideas ?

select
count(ldocumentId),

datepart (Year,dtIssueDate)as [Year],
datepart (Month,dtIssueDate) as [Month]
from documents
group by
dtIssueDate,
datepart (Year,dtIssueDate),
datepart (Month,dtIssueDate)
order by
dtissuedate

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-01 : 06:07:18

Remove dtIssueDate fromgroup by and check.

--------------------------------------------------
S.Ahamed

lemondash
Posting Yak Master

159 Posts

Posted - 2007-06-01 : 06:09:11

Yeah tried that and error's

Server: Msg 8127, Level 16, State 1, Line 1
Column "documents.dtIssueDate" is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-01 : 06:17:45

remove order by clause or put datepart(Year,dtIssueDate) in the order by clause instead of
dtIssueDate

--------------------------------------------------
S.Ahamed

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-01 : 08:26:08

Use

order by
datepart (Year,dtIssueDate),
datepart (Month,dtIssueDate)

at the end

Madhivanan

Failing to plan is Planning to fail

maya_zakry
Constraint Violating Yak Guru

379 Posts

Posted - 2007-06-04 : 02:42:38

hi

what result should we get with this :-
SELECT GETDATE() AS 'Current Date'
GO
i expect this format as stated in the BOL
Here is the result set:

Current Date
---------------------------
Feb 18 1998 11:46PM

but i get this instead :- why?
2007-06-04 14:51:05.140

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-04 : 06:13:38

quote:
Originally posted by maya_zakry

hi

what result should we get with this :-
SELECT GETDATE() AS 'Current Date'
GO
i expect this format as stated in the BOL
Here is the result set:

Current Date
---------------------------
Feb 18 1998 11:46PM

but i get this instead :- why?
2007-06-04 14:51:05.140

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

1 print getdate()
2 If you use front end application, do formation there

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-04 : 09:30:28

quote:
Originally posted by maya_zakry

hi

what result should we get with this :-
SELECT GETDATE() AS 'Current Date'
GO
i expect this format as stated in the BOL
Here is the result set:

Current Date
---------------------------
Feb 18 1998 11:46PM

but i get this instead :- why?
2007-06-04 14:51:05.140

~~~Focus on problem, not solution ¯\(º_o)/¯ ~~~

Maybe BOL is wrong ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-04 : 10:46:46

<<
Maybe BOL is wrong ?
>>

No But expecting formation at QA

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources