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.
| Author |
Topic |
|
d
Starting Member
26 Posts |
 Posted - 2005-07-06 : 04:59:34
|
| HiI am running a query that uses datename() to display the month name and it also uses rollup. I therefore am having trouble getting this to display in Month order. If I use the month integer then I get additional NULL's because of the rollup - Is there anyway to display in month order.Example:SELECT DATENAME(mm, dbo.Proposals.ExpectedDeliveryDate) AS [Month], SUM(dbo.Proposals.AmountOfLoan) AS [Value]FROM dbo.Proposals INNER JOIN dbo.Proposal_Statuses ON dbo.Proposals.Status = dbo.Proposal_Statuses.Status INNER JOIN dbo.Proposal_Types ON dbo.Proposals.ProposalType = dbo.Proposal_Types.ProposalType INNER JOIN dbo.People ON dbo.Proposals.ApplicantID = dbo.People.PersonID INNER JOIN dbo.Dealers ON dbo.Proposals.SupplierID = dbo.Dealers.DealerIDWHERE (dbo.Proposals.ExpectedDeliveryDate IS NOT NULL) AND (dbo.Proposals.ExpectedDeliveryDate > GETDATE())GROUP BY DATENAME(mm, dbo.Proposals.ExpectedDeliveryDate), dbo.Proposals.AmountOfLoan WITH rollupThanks |
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2005-07-06 : 06:57:44
|
| Would be easier with some DDL and example data, but I'm assuming you want all your groups followed by the summary data. If this is the case, use the GROUPING aggregate function to order by first. |
 |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2005-07-06 : 07:09:52
|
| Why not add the "ORDER BY" clause?Without an EXPLICIT ORDER BY clause, you are left to the vagaries of the SQL Engine to decide which order to return the data. GROUPING doesn't include an ORDER automatically. |
|
|
|
mwjdavidson
Aged Yak Warrior
735 Posts |
Posted - 2005-07-06 : 09:15:50
|
| That's a damn good point Andrew!Mark |
|
|
|
|
|
|
Sorting by Month woth Rollup!