CODEWITHSUNDEEP
sqlcounthaving
BigRedEO
BigRedEO

Reputation: 837

Getting correct count SQL

I have a query where I need it to only return results where an event occurs more than once on the same date on the same model/serial number - in this query, whenever the specific function and eventdetails parameters occur more than once in one day. Here is the query -

SELECT DISTINCT store, tintermodel, tinterserial, eventdetails, trunc(datetime)
FROM tinter_events
WHERE tintermodel = 'FM 8000DE'  
AND (datetime >= to_timestamp('2017-05-01', 'YYYY-MM-DD') 
AND datetime < to_timestamp('2017-08-31', 'YYYY-MM-DD'))
AND function = 'Set Colorant Level'
AND eventdetails = 'B1[550]'
GROUP BY store, tintermodel, tinterserial, eventdetails, trunc(datetime)
ORDER BY store, SUBSTR(eventdetails,1,2), trunc(datetime);

Would it be as simple as adding a HAVING COUNT(*) > 1 at the end of the GROUP BY phrase?

Upvotes: 1

Views: 32

Answers (1)

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

Check it by looking at the counts per date per model, from there you would know which records should only show on your query

SELECT trunc(datetime),
       tintermodel,
       COUNT(*)
  FROM tinter_events
 WHERE tintermodel = 'FM 8000DE'
   AND function = 'Set Colorant Level'
   AND eventdetails = 'B1[550]
 GROUP BY trunc(datetime),
          tintermodel
 ORDER BY trunc(datetime)

Upvotes: 1

Related Questions