Search code examples
mysqlsqlmysql-error-1111

Count the number of new records for a given date only if they are new

I am trying to count the number of new records for a given date only if the date of the record is the min(date) for the record owner. Here is the query I am trying to run:

SELECT COUNT(*) 
  FROM user_total_spends 
 WHERE user_id IN (SELECT user_id 
                     FROM user_total_spends 
                    WHERE MIN(DATE(date_posted)) = '2012-02-07')
   AND merchant_location_id = '4f39b201-4a50-40ff-9cdf-cec51506eaf2' 
   AND date_posted = '2012-02-07';

Basically I am trying to say, if this is the first date this user/merchant is encountered, count it as a new user for this merchant.

When I run this I get a Invalid use of group function error. What am I missing?

Solution

  • you must use HAVING to filter a condition for a group of record or an aggregate function, so Instead of WHERE min(date(date_posted)) = '2012-02-07' try HAVING min(date(date_posted)) = '2012-02-07'