query for unique customers
-
i need a query to display unique customers on each of thier last purchase date alone segregating other dates in the same columns for the same customer thanx in advance sathy
If you want help on building a query then you need to tell us what the tables are, what columns are available, what the primary key(s) are on each table and their foreign key counterparts on other tables. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
-
If you want help on building a query then you need to tell us what the tables are, what columns are available, what the primary key(s) are on each table and their foreign key counterparts on other tables. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
the table contains customerId(foreign key), dateOfPurchase, totalValue, shopId(primary key) these are the four columns to be dealth i want to display unique customerId, dateOfPurchase(in specific the last purchase date of a customer), totalValue for the given shopId select distinct customerId, max(dateOfPurchase)as date, totalValue from business_orders where shopId = '9F5DF' group by customerId this query works when totalValue is not added in the query sathy
-
the table contains customerId(foreign key), dateOfPurchase, totalValue, shopId(primary key) these are the four columns to be dealth i want to display unique customerId, dateOfPurchase(in specific the last purchase date of a customer), totalValue for the given shopId select distinct customerId, max(dateOfPurchase)as date, totalValue from business_orders where shopId = '9F5DF' group by customerId this query works when totalValue is not added in the query sathy
imsathy wrote:
this query works when totalValue is not added in the query
That is correct, it must be in the GROUP BY clause or part of an aggregate. If you want the totalValue from the corresponding date you need to create a subquery.
SELECT customerId, [date], totalValue
FROM business_orders AS bo
INNER JOIN (
SELECT customerId, max(dateOfPurchase) AS [date]
FROM business_orders
WHERE shopId = '9F5DF'
GROUP BY customerId) AS ld ON
bo.customerId = ld.customerID AND
bo.[dateOfPurchase] = ld.[date]I also removed the DISTINCT keyword as the GROUP BY effectively does that already and so it was redundant. CAVEAT: If you have two or more orders on the same dateOfPurchace then you will get duplicates for an individual customer. There is nothing you can do about that as there is not enough date to determine which was really the last purchase. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
-
imsathy wrote:
this query works when totalValue is not added in the query
That is correct, it must be in the GROUP BY clause or part of an aggregate. If you want the totalValue from the corresponding date you need to create a subquery.
SELECT customerId, [date], totalValue
FROM business_orders AS bo
INNER JOIN (
SELECT customerId, max(dateOfPurchase) AS [date]
FROM business_orders
WHERE shopId = '9F5DF'
GROUP BY customerId) AS ld ON
bo.customerId = ld.customerID AND
bo.[dateOfPurchase] = ld.[date]I also removed the DISTINCT keyword as the GROUP BY effectively does that already and so it was redundant. CAVEAT: If you have two or more orders on the same dateOfPurchace then you will get duplicates for an individual customer. There is nothing you can do about that as there is not enough date to determine which was really the last purchase. ColinMackay.net "Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius "If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell