Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle/SQL - Grouping items by action by day over time

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!

like image 926
dscl Avatar asked May 20 '11 03:05

dscl


People also ask

Does Datepart work in Oracle?

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.

Can we use count with GROUP BY?

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.

Can you GROUP BY multiple things in SQL?

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.

What is Dateadd in Oracle?

Use this function to add a specified number of days, months, and/or years to a date.


2 Answers

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.

like image 135
MPelletier Avatar answered Nov 15 '22 19:11

MPelletier


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
like image 24
Sanjay Avatar answered Nov 15 '22 19:11

Sanjay