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

Question

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!

MPelletier · Accepted Answer · 2011-05-20T15:04:39.557

26

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.

edited May 20 '11 at 15:04

answered May 20 '11 at 03:44

MPelletier

16,256
15
86
137

Replaced out the date variables and receive this error: "CNT": invalid identifier – dscl May 20 '11 at 12:15
@dscl: Sorry, I couldn't try it against an Oracle db. Please try it now. – MPelletier May 20 '11 at 12:18
No worries; however now I get the same error but for Date_. "DATE_": invalid identifier – dscl May 20 '11 at 14:10
@dscl: Turns out Oracle can only handle column alias names in its `ORDER BY` clause. Bummer. I updated the query for you. – MPelletier May 20 '11 at 15:05
Needed to swap the format and the date string in to_date to get this to work. – Marko Galesic Oct 20 '20 at 20:42

score 1 · Answer 2 · answered May 20 '11 at 03:44

1

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

answered May 20 '11 at 03:44

Sanjay

342
2
9

I added in a where clause on the action = 'rejected' so I don't get back unneeded results. I also get an error though: 'missing expression' – dscl May 20 '11 at 12:18
you are trying in sql Server or Oracle?? – Sanjay May 20 '11 at 17:41

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

2 Answers2