SQL Query for Finding Records Where Count > 1

SQL query for finding records where count 1

Use the HAVING clause and GROUP By the fields that make the row unique

The below will find

all users that have more than one payment per day with the same account number

SELECT 
user_id ,
COUNT(*) count
FROM
PAYMENT
GROUP BY
account,
user_id ,
date
HAVING
COUNT(*) > 1

Update
If you want to only include those that have a distinct ZIP you can get a distinct set first and then perform you HAVING/GROUP BY

 SELECT 
user_id,
account_no ,
date,
COUNT(*)
FROM
(SELECT DISTINCT
user_id,
account_no ,
zip,
date
FROM
payment

)
payment
GROUP BY

user_id,
account_no ,

date
HAVING COUNT(*) > 1

SELECT WHERE COUNT = 1

The query may seem a little counter-intuitive at first. You need to group by MemberId to get the students who only took one class:

select max(l.LessonDate) as LessonDate
from Lesson l
group by l.MemberId
having count(*) = 1;

Because there is only one class for the student, max(l.LessonDate) is that date. Hence this does what you want: it gets all the dates for members who only took one class.

Your line of thinking is also pretty close. Here is the query that I think you were looking for:

SELECT LessonDate 
FROM LESSON
WHERE MemberId in (SELECT l2.MemberId
FROM Lesson l2
GROUP BY l2.MemberId
HAVING COUNT(*) = 1
);

This approach is more generalizable, if you want to get the dates for members that have 2 or 3 rows.

sql select records having count 1 where at lease one record has value

You can use EXISTS:

SELECT  ParticipantId 
FROM Contact
WHERE EXISTS
( SELECT 1
FROM Contact c2
WHERE c2.ParticipantID = c.ParticipantId
AND ContactTypeId = 1
GROUP BY ParticipantID
HAVING COUNT(*) > 1
AND COUNT(CASE WHEN IsCurrent = 0 AND IsActive = 1 THEN 1 END) >= 1
);

Select where count of one field is greater than one

Use the HAVING, not WHERE clause, for aggregate result comparison.

Taking the query at face value:

SELECT * 
FROM db.table
HAVING COUNT(someField) > 1

Ideally, there should be a GROUP BY defined for proper valuation in the HAVING clause, but MySQL does allow hidden columns from the GROUP BY...

Is this in preparation for a unique constraint on someField? Looks like it should be...

Find records having count 1

You checking DubGroupID IN(but selecting count here). Do something as below-

......
AND DubGroupID IN (SELECT DubGroupID
FROM [SOMETABLE]
GROUP BY DubGroupID
HAVING COUNT(DubGroupID) > 1)
.........

How to count rows for groups with a given member?

SELECT album_id, count(*) AS ct
FROM tbl t
JOIN tbl t1 USING (album_id)
WHERE t.user_id = 4
GROUP BY 1
ORDER BY 1; -- optional

I chose this form, out of many possible solutions, because it can use an index on (user_id).

For best performance, you have two indexes: one on (user_id, album_id), and another one on (album_id) (or a multicolumn index with album as leading column). See:

  • Is a composite index also good for queries on the first field?

Assuming user_id is NOT NULL, so count(*) is equivalent, but faster. See:

  • Postgres: count(*) vs count(id)

How to get total number of rows, and latest row's value in MySql?

One way to achieve this would be to use the latestOfMany method. Add the following to your Post model:

public function latestComment()
{
return $this->hasOne(Comment::class)->latestOfMany();
}

Then you can eager load the results:

$posts = Post::with('latestComment')->withCount('comments as total_comments')->get();


Related Topics



Leave a reply



Submit