OraFAQ Forum: SQL & PL/SQL » how to get the total records using LEVEL

Home » SQL & PL/SQL » SQL & PL/SQL » how to get the total records using LEVEL

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

how to get the total records using LEVEL [message #620564]

Mon, 04 August 2014 06:08

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Hi all,

I need the total count of records?

SELECT MAX (sal)
  FROM emp e
 WHERE LEVEL <= 5
  GROUP BY LEVEL
CONNECT BY PRIOR sal > sal
  ORDER BY LEVEL

Report message to a moderator

Re: how to get the total records using LEVEL [message #620568 is a reply to message #620564]

Mon, 04 August 2014 06:43

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I need the total count of records?

What does this mean?
Is this not just "COUNT(*)"?

Report message to a moderator

Re: how to get the total records using LEVEL [message #620569 is a reply to message #620568]

Mon, 04 August 2014 06:46

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Quote:

What does this mean?
Is this not just "COUNT(*)"?

No, Thanks for reply Michel , i need the records count

Report message to a moderator

Re: how to get the total records using LEVEL [message #620570 is a reply to message #620569]

Mon, 04 August 2014 06:49

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

Senior Member

mist598 wrote on Mon, 04 August 2014 17:16

i need the records count

Which records? Explain properly in words.

Report message to a moderator

Re: how to get the total records using LEVEL [message #620571 is a reply to message #620569]

Mon, 04 August 2014 06:51

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

That's what Michel asked.

"Records count" = COUNT(*)

"The highest salary" = MAX(sal)

So, which one do you need? What does LEVEL have to do with counting records (maybe it does, I don't know)? Could you explain that?

Report message to a moderator

Re: how to get the total records using LEVEL [message #620572 is a reply to message #620571]

Mon, 04 August 2014 06:55

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

SQL> SELECT MAX (sal)
  2    FROM emp e
  3   WHERE LEVEL <= 5
  4    GROUP BY LEVEL
  5  CONNECT BY PRIOR sal > sal
  6    ORDER BY LEVEL
  7    /

  MAX(SAL)
----------
      6000
      3000
      2975
      2850
      2450

Suppose i have output like above, then i need the max salary employees count,

Report message to a moderator

Re: how to get the total records using LEVEL [message #620573 is a reply to message #620572]

Mon, 04 August 2014 07:10

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

Senior Member

mist598 wrote on Mon, 04 August 2014 17:25

Suppose i have output like above, then i need the max salary employees count,

I don't understand the purpose of your query.

Anyway, your query will always return the distinct rows based on SAL column due to this :

CONNECT BY PRIOR sal > sal

So the count of rows will be the number of distinct values of SAL.

But once again, I don't understand the purpose of your query.

Report message to a moderator

Re: how to get the total records using LEVEL [message #620574 is a reply to message #620573]

Mon, 04 August 2014 07:13

mist598
Messages: 1195
Registered: February 2013
Location: Hyderabad

Senior Member

Quote:

So the count of rows will be the number of distinct values of SAL.

Ok, how to find the count of this?

Report message to a moderator

Re: how to get the total records using LEVEL [message #620575 is a reply to message #620574]

Mon, 04 August 2014 07:25

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

Senior Member

mist598 wrote on Mon, 04 August 2014 17:43

Quote:
So the count of rows will be the number of distinct values of SAL.

Ok, how to find the count of this?

Count of WHAT? You are yet to explain your requirement in words.

If you just need the count of distict SAL in emp table, then :

SQL> SELECT count(DISTINCT sal) cnt_sal FROM emp
  2  /
   CNT_SAL
----------
        12

Report message to a moderator

Re: how to get the total records using LEVEL [message #620578 is a reply to message #620575]

Mon, 04 August 2014 07:29

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

Senior Member

And I know your next question would be, how to get the count with your posted query. Whatever your requirement is, which you seem to keep it a top level secret.

Use COUNT(*) as Michel already suggested :

SQL> SELECT COUNT(*)
  2    FROM (SELECT COUNT(LEVEL), MAX(sal)
  3            FROM emp e
  4           WHERE LEVEL <= 5
  5           GROUP BY LEVEL
  6          CONNECT BY PRIOR sal > sal
  7           ORDER BY LEVEL)
  8  /
  COUNT(*)
----------
         5

I seriously don't understand purpose of doing all this Shocked