CODEWITHSUNDEEP
sqlsql-serversql-server-2008
goofyui
goofyui

Reputation: 3492

SQL using Count and Group By

I have to Select total number of customers based on State and their most recent orders which is DateFilled.

select CustomerLastName,CustomerFirstName, [State], DateFilled from customer


State  Total#CustByState    RecentDateFilled 
CA        100                04/01/2014
AZ        80                 04/23/2014
OR        120                02/02/2014

Upvotes: 1

Views: 47

Answers (1)

StuartLC
StuartLC

Reputation: 107247

A Simple grouping will give you the Count and latest order, although of course this is the most recent order of Any customer in that state?

SELECT [State], COUNT(*) as TotalCusts, Max(DateFilled) as LastDateFilled
FROM Customer
GROUP By State;

If you need a count of distinct customers (Assuming some key elsewhere on your table):

SELECT [State], COUNT(DISTINCT CustId) as TotalCusts, Max(DateFilled) as LastDateFilled
FROM Customer
GROUP By State;

Upvotes: 3

Related Questions