Solved

sql group by order by

Posted on 2007-11-30
11
6,512 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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
 

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

623 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