Hi all I have a 'widget' table that has the following columns: widget, action, timestamp_. What we want to do is pull all the widgets that were rejected more than once a day between certain dates. So here's an example table
widget action timestamp_
-------------------------------------------
type1 reject 2011-05-10 08:00:00
type1 reject 2011-05-10 09:00:00
type1 reject 2011-05-10 09:30:00
type2 reject 2011-05-11 09:30:00
type3 reject 2011-05-11 09:30:00
type1 reject 2011-05-11 09:30:00
type1 reject 2011-05-11 09:30:00
type2 reject 2011-05-12 10:30:00
type2 reject 2011-05-12 12:30:00
type3 reject 2011-05-12 12:30:00
So I anticipate wanting to see results in one of these two manners....
Between date x and y there were two widgets that were rejected multiple times in single days
This would see that type1 was rejected more than once in a day as was type2 thus the count is: 2
OR
Display each widget along with the date that it was rejected more than once and how many times. Example..
widget date count
---------------------------------
type1 2011-05-10 3
type1 2011-05-11 2
type2 2011-05-12 2
This would probably be the preferred output... but how?
Thanks in advance!
The function works with a set of two arguments, an input date and the name of the part that has to be extracted from it. However, datepart() function works in SQL Server, Oracle, and Azure SQL databases only.
The use of COUNT() function in conjunction with GROUP BY is useful for characterizing our data under various groupings. A combination of same values (on a column) will be treated as an individual group.
The SQL GROUP BY clause is used along with some aggregate functions to group columns that have the same values in different rows. The group by multiple columns technique is used to retrieve grouped column values from one or more tables of the database by considering more than one column as grouping criteria.
Use this function to add a specified number of days, months, and/or years to a date.
This would give your output:
SELECT Widget, to_char(timestamp_,'YYYY-MM-DD'), Count(Widget)
FROM Widget
WHERE timestamp_ BETWEEN to_date('YYYY-MM-DD HH24:MI:SS','%date1%') AND to_date('YYYY-MM-DD HH24:MI:SS','%date2%')
AND action LIKE 'reject'
GROUP BY Widget, to_char(timestamp_,'YYYY-MM-DD')
HAVING Count(Widget) > 1;
Of course, you'll want to replace the date variables.
Try the below one and you will get what you want :-
Select widget,convert(varchar,[timestp],102) As Date,COUNT(timestp) as Count
From MTest
group by widget,[action],convert(varchar,[timestp],102)
having COUNT(timestp)>1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With