CODEWITHSUNDEEP
phpmysqlsql
Vitaly Nevgen
Vitaly Nevgen

Reputation: 67

Counting MySQL GROUP BY

So, I have next table structure :

Table data Image

Is there a way to make SQL query that counts simillar hashes in r_hash column, than founds this hash in hash column and returns an uid, and count of hashes?

For example - uid - 21520578; type - 1; count - 7?

Upvotes: 2

Views: 176

Answers (2)

air4x
air4x

Reputation: 5683

Try the below query 

SELECT T1.uid, T1.type, T2.count 
FROM table T1,
(
  SELECT r_hash, COUNT(*) AS count  
  FROM table 
  GROUP BY r_hash
) T2 
WHERE T1.hash = T2.r_hash

Upvotes: 1

neworld
neworld

Reputation: 7793

You can do that by using join

SELECT t1.uid, t1.type, COUNT(t2.id) as `count`
FROM table AS t1
LEFT JOIN table AS t2 ON t2.r_hash = t1.hash
GROUP BY t1.id

I am not tested this query.

Edit: with left join you also receive rows with count = 0.

Upvotes: 1

Related Questions