CODEWITHSUNDEEP
mysqllaravel-4
I am L
I am L

Reputation: 4632

How to get the count of a grouped by rows - SQL

So I have this table

table: "tbl_hash"

 ------------------------------
| id | hash1 | hash2 | hash3 |
------------------------------
| 1  |   a   |   b   |   c   |
| 2  |   a   |   b   |   c   |
| 3  |   a   |   g   |   d   |
| 4  |   a   |   g   |   d   |
| 5  |   a   |   g   |   d   |
------------------------------

I only want to group them by hash1, hash2, and hash3. count them and only return the count which is higher by 2.

So I have this query to get the values I wanted:

select CONCAT(hash1, hash2, hash3) as hashes, COUNT(*) as count from `tbl_hash` group by hashes having `count` > 2 limit 5

^^ the query above works perfectly..

But what If I wanted to get the data and count for each row? Expected output: 

--------------------------------------
| id | hash1 | hash2 | hash3 | count | 
--------------------------------------
| 1  |   a   |   b   |   c   |   2   |
| 2  |   a   |   b   |   c   |   2   |
| 3  |   a   |   g   |   d   |   3   |
| 4  |   a   |   g   |   d   |   3   |
| 5  |   a   |   g   |   d   |   3   |
--------------------------------------

I'm also planning on converting those to a query builder using DB::table...

Upvotes: 0

Views: 47

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521437

You may join your original table to a subquery which finds the counts for each group:

SELECT t1.*, t2.cnt
FROM tbl_hash t1
INNER JOIN
(
    SELECT hash1, hash2, hash3, COUNT(*) AS cnt
    FROM tbl_hash
    GROUP BY hash1, hash2, hash3
    HAVING COUNT(*) > 2
) t2
     ON t1.hash1 = t2.hash1 AND
        t1.hash2 = t2.hash2 AND
        t1.hash3 = t2.hash3;

Note that what I wrote above would completely filter off any original records belonging to a hash1/hash2/hash3 group which did not have a count greater than 2. If you instead want all records, with the count, then remove the HAVING clause.

As a side note, in databases which support analytic functions, such as SQL Server and Oracle, we could write a much less verbose query using COUNT as an analytic function. At some point, mainstream versions of MySQL will also support this. But for now, we are stuck with doing a join.

Upvotes: 2

Related Questions