Solved

sql group by order by

Posted on 2007-11-30
11
6,503 Views
Last Modified: 2011-04-14
I have a statement which I need to add a order by to but can't figure out the correct way to do it, here is my statement.
I need to sort by dateentered

SELECT CONVERT(varchar, dateentered, 101) AS [Date], COUNT(*) AS Qty
FROM internetcalls
where dateentered >= DATEADD(DAY, -10, GETDATE())
GROUP BY CONVERT(varchar,dateentered, 101)
0
Comment
Question by:mgit
  • 4
  • 4
  • 2
  • +1
11 Comments
 
LVL 5

Expert Comment

by:usarian
ID: 20384202
SELECT CONVERT(varchar, dateentered, 101) AS [Date], COUNT(*) AS Qty
FROM internetcalls
where dateentered >= DATEADD(DAY, -10, GETDATE())
GROUP BY CONVERT(varchar,dateentered, 101)
ORDER BY dateentered DESC

(if you leave off the DESC you will get ascending.  You can also put in ASC)
0
 

Author Comment

by:mgit
ID: 20384237
Sorry that is what I thought but I get
Error 8127: Column name 'internetcalls.dateentered' is invalid in the ORDER BY clause because it is not contained in either an aggregate function or the GROUP BY clause

0
 
LVL 5

Expert Comment

by:usarian
ID: 20384265
SELECT CONVERT(varchar, dateentered, 101) AS [Date], COUNT(*) AS Qty
FROM internetcalls
where CONVERT(varchar,dateentered, 101) >= DATEADD(DAY, -10, GETDATE())
GROUP BY CONVERT(varchar,dateentered, 101)
ORDER BY dateentered DESC
0
 
LVL 5

Assisted Solution

by:usarian
usarian earned 100 total points
ID: 20384281
whatever you group by, everyreference to that column has to be identical to the exact form of the column in the group by, or a form that is derived from the form used in the group by.

Wow that's confusing.. lemme rephrase

you used convert in the GROUP BY clause, but not in  the WHERE clause.
you had to either change the where clause to match the GROUP BY
or vice versa.
0
 

Author Comment

by:mgit
ID: 20384284
Sorry,
usarian I get the same error
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:mgit
ID: 20384298
I have a time in that field as well, could that be the issue?
0
 
LVL 17

Expert Comment

by:Shanmuga Sundaram
ID: 20384310
SELECT CONVERT(varchar, dateentered, 101) AS [Date], COUNT(*) AS Qty From internetcalls Where dateentered >= DateAdd(Day, -10, GETDATE()) GROUP BY CONVERT(varchar,dateentered, 101)  order by CONVERT(varchar,dateentered, 101)
0
 
LVL 23

Accepted Solution

by:
Racim BOUDJAKDJI earned 300 total points
ID: 20384312
try....
SELECT CONVERT(varchar, dateentered, 101) AS [Date], COUNT(*) AS Qty
FROM internetcalls
where dateentered >= DATEADD(DAY, -10, GETDATE())
GROUP BY CONVERT(varchar,dateentered, 101)
ORDER BY CONVERT(varchar, dateentered, 101)  DESC
0
 
LVL 17

Assisted Solution

by:Shanmuga Sundaram
Shanmuga Sundaram earned 100 total points
ID: 20384317
for descending

SELECT CONVERT(varchar, dateentered, 101) AS [Date], COUNT(*) AS Qty From internetcalls Where dateentered >= DateAdd(Day, -10, GETDATE()) GROUP BY CONVERT(varchar,dateentered, 101)  order by CONVERT(varchar,dateentered, 101) desc
0
 
LVL 5

Expert Comment

by:usarian
ID: 20384328
oh yeah.. duh.. the order by.. sorry
0
 

Author Closing Comment

by:mgit
ID: 31411991
Everyone helped but racimo was the best.
Thanks to everyone!
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now