Solved

sql group by order by

Posted on 2007-11-30
11
6,504 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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 video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

770 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