count records having same colomn value as one rec

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

count records having same colomn value as one rec

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-07-17 : 16:24:27

hi

i have table customers where each customer transaction is stored in the table

table fields are customerid, customername,transdsate,item_purchased

i wnat to get count of transactions for each customer.

I know group by would do the trick but i want the count in this manner:

if a customer has three records of info on same date ,it should count that as 1 transaction instead of 3.

How can i achieve it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-07-17 : 17:07:59

SELECT CustomerID, COUNT(DISTINCT TransDate)
FROM Customers
GROUP BY CustomerID

SELECT CustomerID, COUNT(*)
FROM (
SELECT CustomerID, TransDate
FROM Customers
GROUP BY CustomerID, TransDate
) AS d
GROUP BY CustomerID

N 56°04'39.26"
E 12°55'05.63"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-07-18 : 01:59:19

if TransDate has timepart also


SELECT CustomerID, COUNT(*)
FROM (
SELECT CustomerID,DATEADD(dd,DATEDIFF(dd,0,TransDate),0)
FROM Customers
GROUP BY CustomerID,DATEADD(dd,DATEDIFF(dd,0,TransDate),0)
)t
GROUP BY CustomerID

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-07-20 : 17:21:33

Thank you guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources