I am sorry in advance if this question has already been answered. I am still pretty new to SQL.

I have a database that contains client data.

Each row in the database contains a customer_number and a end_record_date (which is either a date in the past or '00:00:00' if the customer is still active).

I need to find all rows of customers that appear at least twice and in the database (so their customer_number appears at least on two rows). But I only want to get the specific rows if for that customer_number they are active on at least two rows (so the value for end_record_date for that specific customer needs to be '00:00:00' on at least two rows).

So this is what I want to find:

In the above example i would want to get the rows 1 and 1287 returned because those meet my criteria. rowid 89 and 346 do not meet my criteria

How would i go on about this?

Im using sqlitestudio on a .sd3b database

CodePudding user response：

You can get the customer_numbers that you want if you group by customer_number and set the condition in the HAVING clause:

SELECT customer_number
FROM tablename
GROUP BY customer_number
HAVING SUM(end_record_date = '00:00:00') >= 2;

To get all the rows of the table that meet your condition, use the operator IN:

SELECT *
FROM tablename
WHERE customer_number IN (
  SELECT customer_number
  FROM tablename
  GROUP BY customer_number
  HAVING SUM(end_record_date = '00:00:00') >= 2
);

See the demo.