HELP! Multi field count

[rmanan]

Please help me in counting number of appearing records show in query

Name Drinks Date purchased Count
John Milk 8/3/13 1
->Mike Beer 8/4/13 2
John Beer 8/4/13 1
Peter Wine 8/6/13 1
-> Mike Beer 8/6/13 2

Counting conditions: counting will continue if NAME and DRINKS are the same

 

[madefemere]

Hello,

You can use the following query to get your result :

SELECT (SELECT Count(DatePurchased) 
  FROM Table1 as T2
  WHERE T2.DatePurchased<T1.DatePurchased AND T2.Name=T1.Name AND T2.Drinks=T1.Drinks)+1 AS Count, *
FROM Table1 AS T1
ORDER BY T1.Name, T1.DatePurchased;

Good continuation