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
Copy Tables from One Database to Another in SQL Server
Are There Any Disadvantages to Always Using Nvarchar(Max)
How to Order by With Union in Sql
Using Union and Order by Clause in MySQL
Pivot on Multiple Columns Using Tablefunc
Is There a Shortcut For Select * From
Operand Should Contain 1 Column - MySQL Not In
Group by to Combine/Concat a Column
Strings as Primary Keys in SQL Database
Postgresql Group by Different from MySQL
Escaping Keyword-Like Column Names in Postgres
How to Output a Select Statement from a Pl/SQL Block
Get Record Counts For All Tables in MySQL Database
Best Way to Check For "Empty or Null Value"
How to Log SQL Statements in Grails
Sqlite Database Default Time Value 'Now'
T-SQL Split String Based on Delimiter
How Can Multiple Rows Be Concatenated into One in Oracle Without Creating a Stored Procedure