CODEWITHSUNDEEP
sqlsql-server-2008
Bill Jones
Bill Jones

Reputation: 701

Finding the maximum total with SQL

I have two SQL tables in a SQL Server 2008 database that look like the following:

Customer
--------
ID
Name

Order
-----
ID
CustomerID
Total

I need to figure out what the most number of order placed by a customer has been. At this point, I've gotten here:

SELECT MAX([OrderCount]) FROM (
  SELECT COUNT(o.[ID]) as 'OrderCount'
  FROM [Order] o 
  GROUP BY o.[CustomerID]
)

When I execute this statement, I get a message that says "Incorrect syntax near ')'". My subquery works. Which would imply the problem is with SELECT MAX([OrderCount]), but everything looks correct to me (granted, i'm not a sql whiz). What am I doing wrong here? Am I even attacking this sql query correctly?

Thank you

Upvotes: 1

Views: 79

Answers (3)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

One more option with OVER clause

SELECT TOP 1 MAX(COUNT(o.[ID])) OVER() AS OrderCount
FROM [Order] o
GROUP BY o.[CustomerID]

Demo on SQLFiddle

Upvotes: 0

xDeveloper
xDeveloper

Reputation: 1

Or you can do this:

SELECT max(COUNT(o.[ID])) as 'OrderCount'
FROM [Order] o  
GROUP BY o.[CustomerID]

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

You are basically there:

SELECT MAX([OrderCount]) FROM (
  SELECT COUNT(o.[ID]) as 'OrderCount'
  FROM [Order] o 
  GROUP BY o.[CustomerID]
) t

You need the alias at the end.

Another way to write this without the subquery is:

  SELECT top 1 COUNT(o.[ID]) as OrderCount
  FROM [Order] o 
  GROUP BY o.[CustomerID]
  order by OrderCount desc

Upvotes: 3

Related Questions