Some simple question (SQL related) ..

[beginner81]

CUSTOMER

CustomerID CustomerName Address
-----------------------------------------
Cust001 John AAA
Cust002 Smith BBB
Cust003 Alan CCC



PRODUCT

ProductID Items CustomerID
--------------------------------------------
Prod001 Shampoo Cust001
Prod002 Pencil Cust001
Prod003 Eraser Cust001
Prod004 Orange Cust002
Prod005 Apple Cust002
Prod006 PineApple Cust003


The data above is 2 tables in the sql server with corresponding field... (CUSTOMER and PRODUCT TABLE)..
So can i know what is the sql statement for determined how many items that Cust001 purchase??
Sql.Add( ........... where ....... ) ?? and how bout if i want to get last record for certain field
from the table (Cos sometimes i need to get the value from last record to do some parsing)..
so in this case, i would like to get the CustomerID who purchase the last items in the table ??

P/S : assume the last items can b any items.. so all i want is just to what is the last record/value
for CuotomerID field in PRODUCT table..

 

[VintageWine]

If ProductID has a unique value for each row of the table

SELECT * FROM PRODUCT WHERE ProductID=(SELECT MAX(ProductID) FROM PRODUCT WHERE CustomerID='Cust002')

If ProductID is not unique then you need another column. Preferable a unique ID, but a timestamp would work, and use that instead of ProductID.

Have fun
Simon

 

[beginner81]

(SELECT MAX(ProductID) FROM PRODUCT WHERE CustomerID='Cust002')

What is the MAX for ?? sorry for the stupid question.. i'm poor in SQL..

 

[LucieLastic]

>So can i know what is the sql statement for determined how many items that Cust001 purchase??

Select count(CustomerID)
from Product
where CustomerID = ‘Cust001’
group by CustomerID

>P/S : assume the last items can b any items.. so all i want is just to what is the last record/value
for CuotomerID field in PRODUCT table..

Select top 1 ProductID, CustomerID from Product
Where customerID = ‘Cust001’
Order by ProductID desc

This second query is assuming that the ProductID is an integer. It would be safer to have another field in the Product table, like BoughtDateTime which contained the date and time the product was bought by the customer. Do that and the query would be :

Select top 1 BoughtDateTime, ProductID, CustomerID from Product
Where customerID = ‘Cust001’
Order by BoughtDateTime desc

I’m also assuming that you’re using MS SQL Server as 'Top' is specific to that, I think.

I've also written these from memory, so hopefully no typos - I don't have access to SQLServer now either.

So, hope this helps, anyway.

lou

 

[VintageWine]

SELECT MAX(ProductID) FROM ..........

Gets the highest value of ProductID. The query will return a single row.

SELECT COUNT(CustomerID) FROM ..................

as Lou has used, counts the number of records with a CustomerID

SELECT COUNT(*) FROM ..................

Counts the number of records

SELECT COUNT(DISTINCT CustomerID) FROM.............

Counts the number of different CustomerIDs. e.g. SELECT COUNT(DISTINCT CustomerID) FROM PRODUCT
gives (using the table in your original post) a one row answer of

3

To compare the two approaches, Lou has used the "TOP" clause where I used a sub-query. Both are good answers to your question. If you want to be a goody goody try both on a table of substantial size and use the faster.

 

[beginner81]

ok.. now i'm clear that

SELECT count(CustomerID)
from Product
where CustomerID = Cust001

statement will return how many product that Cust001 purchase in Product table..
how bout if i want to know ho many items that each of the Customer purchase ...
it's impossible to wrote the statement one by one rite.. any alternative way then??

 

[DaZZleD]

select count(customerID) from Product group by CustomerID;

 

[DaZZleD]

also if you want the CustomerID to appear too, use:

select CustomerID, count(CustomerID) from Product group by CustomerID;

 

[Kocky]

How about a course in SQL ?