Need Count Help

~Bean~ · May 31st, 2005, 02:18 PM

I am trying to return a list of all my customers in "tblCustomers" and also the number of Orders they have, and the Date of the last order. I have 2 tables, table 1 has all Customer info, and table 2 has the Orders...I am stuck on the Count...

Code:

SELECT * ,(SELECT Count(*) FROM tblOrders) As orderCnt FROM tblCustomers

this always returns the same number as "orderCnt"...I havent even gotten to the Last Date part...

help!

-------------------------
Beware of programmers with screwdrivers...

gbianchi · May 31st, 2005, 02:24 PM

select * from
(SELECT customer, Count(*), max(date) FROM tblOrders group by customer) a,
tblcustomers b
where
b.customer = a.customer

try this.. you should check syntaxis b/c i dont know what kind of sql are you using, also i dont have exact names for the fields on the tables...

HTH

Gonzalo

rgerald · May 31st, 2005, 02:35 PM

To get the number of orders and the date of the last order:

Code:

SELECT CustomerID, Count( * ) As OrderCnt, MAX(OrderDate) As LastOrder FROM tblOrders
GROUP BY CustomerID

Rand

~Bean~ · May 31st, 2005, 02:38 PM

Gonzalo:
What does the "a" and "b" mean?

tables

tblCustomers:
CustomerID
CustomerName
etc.

tblOrders
OrderID
OrderDate
CustomerID
etc.

-------------------------
Beware of programmers with screwdrivers...

gbianchi · May 31st, 2005, 02:43 PM

as a and as b to avoid using the full name of the tables..

in sqlserver 7 you can avoid the AS...

HTH

Gonzalo

rgerald · May 31st, 2005, 02:47 PM

Thanks for more info:

Code:

SELECT c.CustomerName, o.OrderCnt, o.LastOrder FROM tblCustomers c
LEFT OUTER JOIN (SELECT CustomerID, Count( * ) As OrderCnt, MAX(OrderDate) As LastOrder FROM tblOrders
GROUP BY CustomerID) o
ON c.CustomerID = o.CustomerID

The o and the c are aliases for the table name so you don't have to write it out in full every time you reference it.

The LEFT OUTER JOIN will show customers without orders also.

Rand

~Bean~ · May 31st, 2005, 02:55 PM

oh...k...gotcha...thx...

so i have:

SELECT * (SELECT CustomerID, Count(*), max(date) FROM tblOrders GROUP BY CustomerID) AS a, tblCustomers AS b WHERE b.CustomerID = a.CustomerID

and I get "Incorrect syntax near the keyword 'AS'." if I remove the 'AS's then I get
"Incorrect syntax near the keyword 'a'."

(I am using SQL Server 7)
??

-------------------------
Beware of programmers with screwdrivers...

gbianchi · May 31st, 2005, 02:58 PM

try with rand query.. look more nice than mine ;)

anyway, you forgot a comma and the from!!!

SELECT * FROM (SELECT CustomerID, Count(*), max(date) FROM tblOrders GROUP BY CustomerID) AS a, tblCustomers AS b WHERE b.CustomerID = a.CustomerID

HTH

Gonzalo

rgerald · May 31st, 2005, 03:18 PM

You would need to modify your last query as follows:

Code:

SELECT * FROM (SELECT CustomerID, Count(*) AS OrderCnt, max(Orderdate) AS LastOrder 
                    FROM tblOrders 
                    GROUP BY CustomerID) AS a, tboCustomers AS b WHERE b.CustomerID = a.CustomerID

This was tested on the SQL Server Northwind sample database, so I'm certain it's correct.

ps: My earlier posting (query) also worked on Northwind.

Rand

rgerald · May 31st, 2005, 03:23 PM

Oops!

That should be tblCustomers instead of tboCustomers.

I mixed up your table and field names with the Northwind table and field names.

Rand