Tony, works great, I could not find a similar example in help or in samples, but seems to be just an expanded where clause that had me stumped. Can even project a temporary table or view with similar clause that produces the desired results. Yep, plan to use the dialog command to define the count variable as needed. I frequently use temp tables, but thanks for the reminder.
Thanks so much, hope you could take advantage of Australia Day. Steve J From: [email protected] [mailto:[email protected]] On Behalf Of Tony Luck Sent: Monday, January 25, 2021 3:52 PM To: [email protected] Subject: Re: [RBASE-L] - Question of Group By and Having Clause No worries, don't forget to drop the temp table after you print your report so it can recreate the table next time you run your querry, you could also have a variable to filter the number of scan instances to make it even more flexible. On Tue, Jan 26, 2021, 6:40 AM Steve Johnson <[email protected]> wrote: Thanks Tony, will give it a try using your statement. If it works, perfect. Steve J Sent from my BlackBerry KeyOne From: [email protected] Sent: January 24, 2021 11:08 PM To: [email protected] Reply-to: [email protected] Subject: Re: [RBASE-L] - Question of Group By and Having Clause I'm on my mobile so I'll be brief. You could use project, than remove unwanted rows. Project temporary scantable using all The above statement can also contain a where clause to limit by a date for instance next remove unwanted from temp table DELETE ROWS FROM scantable WHERE SCANID IN (SELECT SCANID FROM mainscantable GROUP BY scanid HAVING COUNT(scanid) < 20) Just as a quick thought On Mon, Jan 25, 2021, 1:18 PM Steve Johnson <[email protected]> wrote: Is there a means to take the statement below and extract the data from the driving table to either create a temporary table/view and/or drive a report directly? The goal is to have the report provide the detail for only those instances where the SCANID exceeds the count number. An ordinary WHERE clause will display each instance of a unique SCANID in addition to multiple instances of the same SCANID when the report is defined correctly, which I currently have. But the report is long due to single occurrences of SCANID and I am only interested in multiple occurrences of SCANID. Seems like a temporary table/view would be ideal to drive the report. SELECT scanid COUNT(*) FROM scan1 GROUP BY scanid HAVING COUNT(*) > 20 Thank You Steve Johnson -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/000c01d6f2c8%24be80a050%243b81e0f0%24%40sprintmail.com <https://groups.google.com/d/msgid/rbase-l/000c01d6f2c8%24be80a050%243b81e0f0%24%40sprintmail.com?utm_medium=email&utm_source=footer> . -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/CABnhDAQy-z9nKD%3Dtp8sUT89H%3D90ThtwjP6NJc7nJT%2BSbENCtUw%40mail.gmail.com <https://groups.google.com/d/msgid/rbase-l/CABnhDAQy-z9nKD%3Dtp8sUT89H%3D90ThtwjP6NJc7nJT%2BSbENCtUw%40mail.gmail.com?utm_medium=email&utm_source=footer> . -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/5niamsqp147ji0amluc9somo.1611607239589%40sprintmail.com <https://groups.google.com/d/msgid/rbase-l/5niamsqp147ji0amluc9somo.1611607239589%40sprintmail.com?utm_medium=email&utm_source=footer> . -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/CABnhDARvZ-YtK80bdT-TUdRQH%2B_yE%2BuhZwa9sX1GRunfmKsUZA%40mail.gmail.com <https://groups.google.com/d/msgid/rbase-l/CABnhDARvZ-YtK80bdT-TUdRQH%2B_yE%2BuhZwa9sX1GRunfmKsUZA%40mail.gmail.com?utm_medium=email&utm_source=footer> . -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/006101d6f395%248063d030%24812b7090%24%40sprintmail.com.
