CODEWITHSUNDEEP
laraveleloquent
Billy
Billy

Reputation: 2963

How to select duplicate rows and group by two columns

I have the following table:

ID|user_id|group_id|subject |book_id
 1| 2     |3       |history |1
 2| 4     |3       |history |1
 3| 5     |3       |art     |2
 4| 2     |3       |art     |2
 5| 1     |4       |sport   |5

I would like to list all rows for group 3(id) that have duplicate rows with the same subject_id and book_id. The subject and book_id is what would determine the 2 or more rows to be duplicate.

I would like my distinct results to look like this:

|subject |book_id|
|history |1      |
|art     |2      |

Using either query builder or eloquent

Upvotes: 2

Views: 14800

Answers (1)

peterm
peterm

Reputation: 92785

A SQL query to get the desired result may look 

SELECT subject, book_id
  FROM table1
 WHERE group_id = 3
 GROUP BY subject, book_id
HAVING COUNT(*) > 1

Here is a SQLFiddle demo

Now the same using the Laravel Query Builder

$duplicates = DB::table('table1')
    ->select('subject', 'book_id')
    ->where('group_id', 3)
    ->groupBy('subject', 'book_id')
    ->havingRaw('COUNT(*) > 1')
    ->get();

Upvotes: 13

Related Questions