• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6522
  • Last Modified:

sql group by order by

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
mgit
Asked:
mgit
  • 4
  • 4
  • 2
  • +1
3 Solutions
 
usarianCommented:
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
 
mgitAuthor Commented:
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
 
usarianCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
usarianCommented:
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
 
mgitAuthor Commented:
Sorry,
usarian I get the same error
0
 
mgitAuthor Commented:
I have a time in that field as well, could that be the issue?
0
 
Shanmuga SundaramDirector of Software EngineeringCommented:
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
 
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
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
 
Shanmuga SundaramDirector of Software EngineeringCommented:
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
 
usarianCommented:
oh yeah.. duh.. the order by.. sorry
0
 
mgitAuthor Commented:
Everyone helped but racimo was the best.
Thanks to everyone!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now