Getting correct count SQL

Question

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?

My problem is I'm not sure if that's returning what I want (the query takes a good 15 minutes to run each time. — BigRedEO
– BigRedEO, Commented Sep 13, 2017 at 17:53

Ferdinand Gaspar · Accepted Answer · 2017-09-13 21:55:34Z

1

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)

answered Sep 13, 2017 at 21:55

Ferdinand Gaspar

2,0831 gold badge10 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BigRedEO Over a year ago

Thank you - a slightly edited version of that worked for what I need!

Collectives™ on Stack Overflow

Getting correct count SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related