OraFAQ Forum: SQL & PL/SQL » Filter by Count of particular field

Home » SQL & PL/SQL » SQL & PL/SQL » Filter by Count of particular field

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Filter by Count of particular field [message #615359]

Mon, 02 June 2014 16:45

rlsublime
Messages: 10
Registered: March 2014

Junior Member

I have the following query that I need to filter with a count of >2 for a particular field. I am trying to fix the "having count(SUPERVISOR_NAME)>2" as the current query generates an error.

Select ID, first_last, SITENAME,UTILIZATION,REPORTED,OVERALL,monthYear,SUPERVISOR_NAME
from MONTHLY_SUM
where monthYear
in ('OCT-13','NOV-13','DEC-13','JAN-14','FEB-14','MAR-14','APR-14','MAY-14') and SUPERVISOR_NAME is not null
having count(SUPERVISOR_NAME) > 2;

Thanks

Report message to a moderator

Re: Filter by Count of particular field [message #615360 is a reply to message #615359]

Mon, 02 June 2014 16:49

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

HAVING requires GROUP BY, http://docs.oracle.com/cd/E16655_01/server.121/e17322/sqlqr05001.htm#SQLQR679

--
update: fixed the URL

[Updated on: Mon, 02 June 2014 16:50]

Report message to a moderator

Re: Filter by Count of particular field [message #615361 is a reply to message #615360]

Mon, 02 June 2014 16:52

rlsublime
Messages: 10
Registered: March 2014

Junior Member

I have tried adding the group by but it didnt seem to work. Can a group by be included in a where statement. Can you please advise how to write the query correctly? Thanks

Report message to a moderator

Re: Filter by Count of particular field [message #615362 is a reply to message #615361]

Mon, 02 June 2014 16:53

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

What SQL did you try?

Report message to a moderator

Re: Filter by Count of particular field [message #615363 is a reply to message #615362]

Mon, 02 June 2014 17:05

rlsublime
Messages: 10
Registered: March 2014

Junior Member

Select ID, first_last, SITENAME,UTILIZATION,REPORTED,OVERALL,monthYear,SUPERVISOR_NAME
from MONTHLY_SUM
where monthYear
in ('OCT-13','NOV-13','DEC-13','JAN-14','FEB-14','MAR-14','APR-14','MAY-14') and SUPERVISOR_NAME is not null
having count(SUPERVISOR_NAME) > 2
group by ID, first_last, SITENAME,UTILIZATION,REPORTED,OVERALL,monthYear,SUPERVISOR_NAME

Report message to a moderator

Re: Filter by Count of particular field [message #615364 is a reply to message #615363]

Mon, 02 June 2014 17:08

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

I did give you the link to the doc. Follow the syntax: you can't put different clauses in any order or like. Aggregation (aka GROUP BY) must come before group selection (aka HAVING)

[Updated on: Mon, 02 June 2014 17:08]

Report message to a moderator

Re: Filter by Count of particular field [message #615365 is a reply to message #615364]

Mon, 02 June 2014 17:38

rlsublime
Messages: 10
Registered: March 2014

Junior Member

I tried the following code and got a "not a group by expression" error

Select ID, first_last, SITENAME,UTILIZATION,REPORTED,OVERALL,monthYear,SUPERVISOR_NAME
from MONTHLY_SUM
where monthYear
in ('OCT-13','NOV-13','DEC-13','JAN-14','FEB-14','MAR-14','APR-14','MAY-14') and SUPERVISOR_NAME is not null
Group by ID, first_last,SITENAME,UTILIZATION,REPORTED,OVERALL,monthYear,SUPERVISOR_NAME
having count(SUPERVISOR_NAME) > 2;

Report message to a moderator

Re: Filter by Count of particular field [message #615366 is a reply to message #615365]

Mon, 02 June 2014 17:42

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

My guess is that your statement returns no rows. But as I can't see your screen, I am only guessing.

Report message to a moderator

Re: Filter by Count of particular field [message #615368 is a reply to message #615366]

Mon, 02 June 2014 17:53

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

Sorry, I misread your post. You cannot apply HAVING to a column on which you are grouping. Logically impossible. Look at this:

12c orclz>
12c orclz> select deptno,count(ename) from emp group by deptno having count(ename) > 4;

    DEPTNO COUNT(ENAME)
---------- ------------
        30            6
        20            5

12c orclz>

Report message to a moderator

Re: Filter by Count of particular field [message #615369 is a reply to message #615365]

Mon, 02 June 2014 21:01

rlsublime
Messages: 10
Registered: March 2014

Junior Member

I am trying to combine the following two queries. The first filters by supervisor name and the second pulls in the remaining data. I am not sure how to combine the two to only display the filtered results.

Select SUPERVISOR_FIRST_LAST

from PROD_SUM

where monthYear in ('OCT-13','NOV-13','DEC-13','JAN-14','FEB-14','MAR-14','APR-14','MAY-14')

and SUPERVISOR_FIRST_LAST is not null

group by SUPERVISOR_SID,SUPERVISOR_FIRST_LAST

having count(SUPERVISOR_FIRST_LAST)>3

Select id, first_last, SITENAME,UTILIZATION,REPORTED,OVERALL, monthYear

from PROD_SUM

where monthYear

in ('OCT-13','NOV-13','DEC-13','JAN-14','FEB-14','MAR-14','APR-14','MAY-14')

Report message to a moderator

Re: Filter by Count of particular field [message #615370 is a reply to message #615369]

Mon, 02 June 2014 21:12

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Realize that we don't have your table or data.

SQL> connect scott/tiger
Connected.
SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> select e.ename employee,b.ename boss from emp e, emp b
  2  where e.mgr = b.empno;

EMPLOYEE   BOSS
---------- ----------
FORD       JONES
SCOTT      JONES
JAMES      BLAKE
TURNER     BLAKE
MARTIN     BLAKE
WARD       BLAKE
ALLEN      BLAKE
MILLER     CLARK
ADAMS      SCOTT
CLARK      KING
BLAKE      KING

EMPLOYEE   BOSS
---------- ----------
JONES      KING
SMITH      FORD

13 rows selected.

Report message to a moderator

Re: Filter by Count of particular field [message #615371 is a reply to message #615370]

Mon, 02 June 2014 21:24

rlsublime
Messages: 10
Registered: March 2014

Junior Member

I realize that which is why I posted the query. If you can help me that would be much appreciated. Thanks

Report message to a moderator

Re: Filter by Count of particular field [message #615372 is a reply to message #615371]

Mon, 02 June 2014 21:31

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> If you can help me that would be much appreciated. Thanks
I showed you how to solve your problem.
Adjust your query to be similar to mine.

Report message to a moderator

Re: Filter by Count of particular field [message #615374 is a reply to message #615371]

Mon, 02 June 2014 23:49

oralover2006
Messages: 144
Registered: January 2010
Location: India

Senior Member

rlsublime wrote on Tue, 03 June 2014 07:54

I realize that which is why I posted the query. If you can help me that would be much appreciated. Thanks

try to understand. these professionals can solve our problem but simultaneously want to TRAIN us to write SQL properly.

[Updated on: Mon, 02 June 2014 23:51]

Report message to a moderator

Re: Filter by Count of particular field [message #615375 is a reply to message #615374]

Tue, 03 June 2014 00:02

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>try to understand. these professionals can solve our problem but simultaneously want to TRAIN us to write SQL properly.
does TRAIN mean to simply write your query for you?
If you can't learn by example, perhaps you are unqualified to be trained.

How can we write SQL for you when we do not have your table definition(s).

Report message to a moderator

Re: Filter by Count of particular field [message #615378 is a reply to message #615375]

Tue, 03 June 2014 00:26

oralover2006
Messages: 144
Registered: January 2010
Location: India

Senior Member

BlackSwan wrote on Tue, 03 June 2014 10:32

>
does TRAIN mean to simply write your query for you?
If you can't learn by example, perhaps you are unqualified to be trained.

1) no
2) absolutely
what I wrote I mentioned it to OP.
He should try given link and other examples to learn and understand how it works, if any problem, raise question.

Report message to a moderator

Re: Filter by Count of particular field [message #615480 is a reply to message #615378]

Wed, 04 June 2014 09:02

live4learn
Messages: 41
Registered: September 2013
Location: Bangalore, India

Member

Try this :
Select ID, first_last, SITENAME,UTILIZATION,REPORTED,OVERALL,monthYear,SUPERVISOR_NAME
from (
Select count(1) cnt,ID, first_last, SITENAME,UTILIZATION,REPORTED,OVERALL,monthYear,SUPERVISOR_NAME
from MONTHLY_SUM
where monthYear
in ('OCT-13','NOV-13','DEC-13','JAN-14','FEB-14','MAR-14','APR-14','MAY-14') and SUPERVISOR_NAME is not null
Group by ID, first_last,SITENAME,UTILIZATION,REPORTED,OVERALL,monthYear,SUPERVISOR_NAME
) where cnt >2;

I am assuming your group by is working fine as per your expectation!

Report message to a moderator

Re: Filter by Count of particular field [message #615487 is a reply to message #615359]

Wed, 04 June 2014 11:32

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

As an aside from your SQL problem, it appears you may have a fundamental design flaw in your table.

What is the data type of 'monthYear'?

It should be a DATE. Even if all you are ever interested in is the month and year, you could default the day to the first day of the month and the time component to midnight. If you are trying to store it as a character string, then there is no qualitative difference between monthyear='Jun-14' and monthyear='heresyersign'. Even if you were able to guarantee the quality of the data (you can't, btw) they won't sort or compare properly. As strings, 'APR-14' is before 'SEP-12'.

And you should never be using 2-digit years. I was part of the army that fought that battle 15 years ago.

Report message to a moderator

Re: Filter by Count of particular field [message #615489 is a reply to message #615487]

Wed, 04 June 2014 12:17

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

@Ed, Even I was a part of the y2k/RR combat. However, there are still legacy applications which are used to such design flaws. And developers are bound to violate the rules. Now, what to do? Poor developer.

Report message to a moderator

Previous Topic:	how to user REGEXP for inserting data from one table to another table
Next Topic:	MySQL to oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 05:33:51 CDT 2024