CODEWITHSUNDEEP
mysqlsql
Saint Robson
Saint Robson

Reputation: 5525

MySQL Grouping With Conditional

I have data like this :

+---------------+------+
| timestamp     | robo |
+---------------+------+
| 1518259341785 | A    |
| 1518259341785 | A    |
| 1518259341785 | A    |
| 1518259341785 | RE   |
+---------------+------+

and this :

+---------------+------+
| timestamp     | robo |
+---------------+------+
| 1518259341788 | RE   |
| 1518259341788 | RE   |
| 1518259341788 | RE   |
| 1518259341788 | A    |
+---------------+------+

as we all know, if we group a data and count the rows, using this SQL :

SELECT timestamp, robo, COUNT(*) AS num_rows
FROM tables
GROUP BY timestamp

num_rows will be 4. how can I group data with condition only if robo RE = 1 in a timestamp. so that 1518259341788 group won't count / appear. thank you.

update : result of grouping without conditional :

+---------------+------+----------+
| timestamp     | robo | COUNT(*) |
+---------------+------+----------+
| 1518259341785 | A    |        4 |
| 1518259341788 | A    |        4 |
+---------------+------+----------+

expected result of grouping with conditonal :

+---------------+------+----------+
| timestamp     | robo | COUNT(*) |
+---------------+------+----------+
| 1518259341785 | A    |        4 |
+---------------+------+----------+

Upvotes: 0

Views: 60

Answers (3)

Andreas
Andreas

Reputation: 159086

If you want to get a count of records for each timestamp value, but only if the count of RE records for that timestamp value is 1, then you can do it like this:

SELECT timestamp, COUNT(*) AS num_rows
  FROM tables
 GROUP BY timestamp
HAVING SUM(CASE WHEN robo = 'RE' THEN 1 ELSE 0 END) = 1

Upvotes: 2

isaace
isaace

Reputation: 3429

This query should work:

SELECT timestamp, robo, COUNT(*) AS num_rows
FROM mytables
WHERE timestamp 
NOT IN (SELECT b.timestamp FROM mytables b
        JOIN (SELECT timestamp, count(*) FROM mytables
              WHERE robo = 'RE' 
              GROUP BY timestamp 
              HAVING COUNT(*) > 1) 
        a on a.timestamp = b.timestamp )
GROUP BY timestamp, robo

The results would be:

timestamp                  robo num_rows     
1518259341785               RE  1
1518259341785               A   3

I am not sure how the results you are expecting is possible.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

You could use an inner join on a subselect for timestamp with just a count = 1 for robo = RE

  SELECT timestamp, robo, COUNT(*) AS num_rows
  FROM tables
  INNER join (
    select timestamp
    from tables
    where robo ='RE'
    group by timestamp 
    having count(*) = 1
    ) t on tables.timestamp = t.timestamp
  GROUP BY timestamp

Upvotes: 1

Related Questions