CODEWITHSUNDEEP
sqlgroup-byaggregate-functions
still_learning
still_learning

Reputation: 806

How to find frequency in SQL

I am having some issues with SQL code, specifically in finding the frequency of an ID.

My table looks like 

Num  ID
136   23
1427  45
1415  67
1416  23
7426  45
4727  12
4278  67
...

I would need to see the frequency of ID, when this has more or equal 2 same values.

For example: 23, 45 and 67 in the table above.

I have tried as follows:

Select distinct *, count(*)
From table_1
Group by 1,2
Having count(*) >2

But it is wrong. I need distinct, as I do not want any duplicates in Num. I think I should you a counter to reset when the value of the next rows is different from the previous one and report the frequency (1, 2, 3, and so on), then select values greater or equal to 2, but Indo not know how to do it in Sql. Could you help me please? Thanks

Upvotes: 0

Views: 922

Answers (3)

Coskun Ozogul
Coskun Ozogul

Reputation: 2469

This works for me

    SELECT ID, COUNT(ID) AS Frq FROM MyTable
    GROUP BY ID 
    HAVING COUNT(ID) > 2 
    ORDER BY COUNT(ID) DESC

Upvotes: 1

Joe Taras
Joe Taras

Reputation: 15379

If I understand your question, you can try this:

SELECT ID, COUNT(1)
FROM table_1
GROUP BY ID
HAVING COUNT(1) >= 2

In this way you have the ID's with 2 or more occurences and the number of occurences

EDIT I suppose you are using MySql but add your DBMS in your question, so, try this:

SELECT ID, COUNT(1) as FREQUENCY, GROUP_CONCAT(NUM)
FROM table_1
GROUP BY ID
HAVING COUNT(1) >= 2

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Use ID only in GROUP BY :

SELECT ID, COUNT(*) AS No_frequency
FROM table t
GROUP BY id
HAVING COUNT(*) >= 2;

Note : If you have duplicate num then use distinct :

HAVING COUNT(DISTINCT num) >= 2;

Upvotes: 2

Related Questions