Solved

sql group by order by

Posted on 2007-11-30
11
6,501 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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

758 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

22 Experts available now in Live!

Get 1:1 Help Now