CODEWITHSUNDEEP
mysqlcountrowsmatching
zDogwatch
zDogwatch

Reputation: 57

Trying to display matching rows that match an ID counted 3 or less times. Single table

I'm pretty new to SQL and mySQL, I'm self taught and attempting to write a query that shows me all rows that have a count of 3 or less of an ID that is matching in the ID column.

So for example:

Id       timestamp          Number            
--       ---------          ---
1        0001-12-01         1001
1        0001-12-02         3520
1        0001-12-01         1002
2        0001-12-02         2152
2        0001-12-01         1005
2        0001-12-02         1250
2        0001-12-01         1007
2        0001-12-02         1536
3        0001-12-01         1009
2        0001-12-02         1305
3        0001-12-01         1010
2        0001-12-02         1125
3        0001-12-01         1107
2        0001-12-02         1108

Ideally, the result would show: 

Id       timestamp          Number            
--       ---------          ---
1        0001-12-01         1001
1        0001-12-01         1002
1        0001-12-02         3520
3        0001-12-01         1009
3        0001-12-01         1010
3        0001-12-01         1107

This recognises that both ID "1" and ID "3" have 3 or less matching/counted IDs and displays the results by whatever filter I have set in place.

I've managed to write a query that counts the rows and only shows the the counts that are 3 or less, but this groups them by their ID's and doesn't display the rows. That looks like this:

select 
    concat(t1.id) as 'ID',
    t1.timestamp  as 'timestamp',
    count(t1.id) as 'Number'
from 
    table1 t1
where -- Curly braces are Metabase variables
    t1.timestamp between {{startdate}} and {{enddate}} 
group by t1.id
having count(*) < 3
order by id
limit 1000

I've done some searches around SO and other resources, but have come up dry and was hoping someone would be able to give me a hand or a push in the right direction. Any help is appreciated.

Upvotes: 1

Views: 44

Answers (3)

ScaisEdge
ScaisEdge

Reputation: 133400

You could use a join with the subquery count(*) group by id 

select * from table1  t1
    inner join  (

      select id
      from table1 
      group by  id 
      having count(*) <= 3 

    ) t on t.id  = t1.id

Upvotes: 0

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48207

 SELECT t1.*
 FROM YourTable t1
 WHERE id IN ( SELECT t2.id
               FROM YourTable t2
               WHERE t2.timestamp between {{startdate}} and {{enddate}} 
               GROUP BY t2.id
               HAVING COUNT(*) <= 3)
   AND t1.timestamp between {{startdate}} and {{enddate}} 
 ORDER BY t1.id

Upvotes: 1

Barmar
Barmar

Reputation: 782508

You need to join the original table with the IDs found in a grouped query.

SELECT table1.*
FROM table1
JOIN (
    SELECT id
    FROM table1
    HAVING COUNT(*) <= 3
    GROUP BY id
) AS grouped ON table1.id = grouped.id

Also, you need to use <= 3 rather than < 3.

Upvotes: 1

Related Questions