sql query

hraja77
hraja77 used Ask the Experts™
on
Hi,

I have a table which logs all reboots of a till on our system;
I need a query to see on a particular day how many tills rebooted - problem is that some times a till may reboot 3 times in a day and record three records into my table but i only need to show this as one:

my table is called audit: columns are:

id
timestamp
tillname

thanks
H
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
select tillname, to_char(timestamp,'mm-dd-yyyy') as Reboot_Day, count(*)
from audit
group by tillname, to_char(timestamp,'mm-dd-yyyy')

Author

Commented:
hi,

on one day the till will reboot more than once but i only want to record the fact it rebooted once

thanks
H
Commented:
select distinct tillname, day(timestamp) + '/' + month(timestamp) + '/' + year(timestamp) as Reboot_Day
from audit
group by tillname











hope that helps
- SK
Gerwin Jansen, EE MVETopic Advisor
Most Valuable Expert 2016

Commented:
Can you show a sample of your required output? The answer that rmandra gave looks OK to me. Do you want to show just the days that have reboots? Then it's just a distinct, something like this:

select distinct(to_char(timestamp,'mm-dd-yyyy'))
from audit;
Hamed NasrRetired IT Professional

Commented:
Try to select till_id  , max(reboot time) , and count(),  Grouping By till_id.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial