Home » SQL & PL/SQL » SQL & PL/SQL » Count (Oracle 9i)
Count [message #442645] Tue, 09 February 2010 13:02 Go to next message
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
Member
Hi experts,
Can any one help me to write a SQl query
to count number of rows in a table
without using any Group functions .
Please help me.
Thanks in advance.
Re: Count [message #442647 is a reply to message #442645] Tue, 09 February 2010 13:32 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Well, if you want to count all of them you don't need any group by.
Re: Count [message #442648 is a reply to message #442645] Tue, 09 February 2010 13:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Fetch all rows and count them one by one (or batch by batch).

Regards
Michel

[Updated on: Tue, 09 February 2010 13:33]

Report message to a moderator

Re: Count [message #442665 is a reply to message #442645] Tue, 09 February 2010 16:24 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
The answers here represent good information. But to answer you original question directly... to my knowledge NO there is no way to count rows in a table using a SQL query, without a GROUP function.

As has been indicated, there are indirect ways to find the number of rows in a table.

1) collect statistics then immediately check USER_TABLES for a rowcount.

2) execute a plsql loop and count rows from the loop.

3) issue an update of all rows and look at the number of rows updated.

4) maybe something else someone smarter than I can come up with.

But none of these are a SQL statement.

Kevin
Re: Count [message #442719 is a reply to message #442645] Wed, 10 February 2010 03:23 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
<pedant>
This uses no aggregate functions - it uses analytic functions instead:
create table test_142 as (select level col_1 from dual connect by level <= 46);      
      
select distinct count(*) over (partition by null)
from   test_142;

</pedant>
Re: Count [message #442722 is a reply to message #442645] Wed, 10 February 2010 03:44 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Is this another dumb455 interview question?
Re: Count [message #442724 is a reply to message #442722] Wed, 10 February 2010 04:08 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Probably. And as most of the time the only answer that would make sense in an interview would be "Why the hell would you want to do that?"

Re: Count [message #442727 is a reply to message #442724] Wed, 10 February 2010 04:31 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Agreed, I must admit, I've never had any stoopid questions like that in interviews, I sometimes wonder exactly how I would react. I suppose it would depend on how much I wanted that particular job...
Re: Count [message #442739 is a reply to message #442645] Wed, 10 February 2010 05:50 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
well if we want to get picky about it then...

Analytics are aggregates.

If an aggregate function is a function that returns a result based on a set of rows then Analytics certainly fit this bill.

Kevin
Re: Count [message #442742 is a reply to message #442645] Wed, 10 February 2010 05:59 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Depends how you look at it. Analytics don't aggregate rows together in the result set.
Re: Count [message #442748 is a reply to message #442645] Wed, 10 February 2010 06:18 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Quote:
Analytics don't aggregate rows together in the result set.


Hmm... so tell me, what is your definition of aggregation?

I think you will see that analytics do indeed aggregate rows together once you consider the definition of an aggregate function.

Kevin
Re: Count [message #442749 is a reply to message #442748] Wed, 10 February 2010 06:41 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
I would also consider analytic functions to be aggregate functions.

define:aggregation
Quote:
collection: several things grouped together or considered as a whole


So since analytic functions look at several rows to get a result, they fit that description, even if the don't group them together.

Of course the original question never came close to being that well defined, since by definition the "count" of lines in a table requires looking at them as "A Group", so basically the question was how to look at a group without looking at a group.


Re: Count [message #442754 is a reply to message #442645] Wed, 10 February 2010 06:50 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks Thomas.

I would even go further and say that analytics do group rows together. Otherwise how does one expalain PARTITION BY () in the analytic syntax. In the case of analytics PARTITION = GROUP OF ROWS.

What do you think?

Kevin
Re: Count [message #442757 is a reply to message #442754] Wed, 10 February 2010 07:01 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
They definitely group them together in the sense that they look at the group.

What they don't to is group in the sense of integrate/merge, so that they only return one "summary" row instead of the single rows.

In that there you probably have 2 different characteristics of functions:

1) how do they look at the data
2) how do they output the data
Re: Count [message #442759 is a reply to message #442645] Wed, 10 February 2010 07:06 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I guess we could go round and round on this point so I am going to stop. I say they do "intergrate/merge" and they do "return a summary row".

Maybe if you could provide more detail on your perspective my dense head could absorbe what you are saying.

Kevin
Re: Count [message #442760 is a reply to message #442759] Wed, 10 February 2010 07:21 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'd say that the the big difference is this:

Aggregate functions aggregate rows together, giving you fewer rows in your result set.

Analytic function aggregate values across rows, giving you back the same number of rows that you started with.
Re: Count [message #442761 is a reply to message #442760] Wed, 10 February 2010 07:31 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
And i can put this link here....How are analytic functions different from group or aggregate functions?

sriram Smile
Re: Count [message #442762 is a reply to message #442645] Wed, 10 February 2010 07:35 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think it's best stated like this:

Aggregates aggregate rows of data together into a sub-set of rows specified by the group by (or 1 row otherwise), you'll never get back the number of rows identified by the where clause with an aggregate, unless each row is it's own group - in which case you aren't really aggregating anything.

Analytics aggregate a column over multiple rows specified by the partition clause, but the rows themselves aren't aggregated so the number of rows return will be the same as the number of rows identified by the where clause.

So to nick an example from a thread from ages back:
CREATE TABLE myenterprise(
city VARCHAR2(10), 
storeid VARCHAR2(10),
month_name VARCHAR2(10),
total_sales NUMBER);

INSERT INTO myenterprise(city, storeid, month_name, total_sales)
  VALUES ('paris', 'id1', 'January', 1000);
INSERT INTO myenterprise(city, storeid, month_name, total_sales)
  VALUES ('paris', 'id1', 'March', 7000);
INSERT INTO myenterprise(city, storeid, month_name, total_sales)
  VALUES ('paris', 'id1', 'April', 2000);
INSERT INTO myenterprise(city, storeid, month_name, total_sales)
  VALUES ('paris', 'id2', 'November', 2000);
INSERT INTO myenterprise(city, storeid, month_name, total_sales)
  VALUES ('paris', 'id3', 'January', 5000);
INSERT INTO myenterprise(city, storeid, month_name, total_sales)
  VALUES ('london', 'id4', 'Janaury', 3000);
INSERT INTO myenterprise(city, storeid, month_name, total_sales)
  VALUES ('london', 'id4', 'August', 6000);
INSERT INTO myenterprise(city, storeid, month_name, total_sales)
  VALUES ('london', 'id5', 'September', 500);
INSERT INTO myenterprise(city, storeid, month_name, total_sales)
  VALUES ('london', 'id5', 'November', 1000);

SQL> SELECT city,
  2  SUM(total_sales) OVER (PARTITION BY city) AS TOTAL_SALES_PER_CITY
  3  FROM myenterprise
  4  ORDER BY city;

CITY       TOTAL_SALES_PER_CITY
---------- --------------------
london                    10500
london                    10500
london                    10500
london                    10500
paris                     17000
paris                     17000
paris                     17000
paris                     17000
paris                     17000

9 rows selected.

SQL> SELECT city, 
  2  SUM(total_sales)AS TOTAL_SALES_PER_CITY
  3  FROM myenterprise
  4  GROUP BY city
  5  ORDER BY city;

CITY       TOTAL_SALES_PER_CITY
---------- --------------------
london                    10500
paris                     17000

SQL> 


To be honest this is largely semantics. Having looked in the docs oracle describes analytic functions as a specialised set of aggregates.
It really just boils down to whether you're talking about aggregating the resultset rows in their entirety or just aggregating specific columns over the rows.
Re: Count [message #442764 is a reply to message #442645] Wed, 10 February 2010 07:52 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
hehehe...

Seems to me each post only shows that analytics are aggregates.

Some of us are confusing the work done by the analytic with the place where the result is shown. The values returned by analytics are aggregate values. They are computed by aggregating across a set of rows. The fact that this aggregation is repeated in a controling query many times does not change the basic nature of what the analytic is doing.

I like best however cookemonster's note from the Oracle docs. Thank you for looking that up for us.

And my thanks to everyone for their opinions.

Kevin
Re: Count [message #442765 is a reply to message #442759] Wed, 10 February 2010 07:56 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Kevin Meade wrote on Wed, 10 February 2010 13:06
I say they do "intergrate/merge" and they do "return a summary row".


I'd quibble with that as well - analytics don't return a summary row, they return rows with summaries embedded in them. Not obvious from my example but if we add some extra columns to the analytic query:
SQL> SELECT city,
  2  storeid,
  3  total_sales,
  4  SUM(total_sales) OVER (PARTITION BY city) AS TOTAL_SALES_PER_CITY
  5  FROM myenterprise
  6  ORDER BY city;

CITY       STOREID    TOTAL_SALES TOTAL_SALES_PER_CITY
---------- ---------- ----------- --------------------
london     id5                500                10500
london     id4               6000                10500
london     id4               3000                10500
london     id5               1000                10500
paris      id2               2000                17000
paris      id1               2000                17000
paris      id1               7000                17000
paris      id1               1000                17000
paris      id3               5000                17000


There are no summary rows there for the cities. Each gives you part of the picture plus an embedded summary.
Re: Count [message #442776 is a reply to message #442765] Wed, 10 February 2010 08:34 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You could also make the distinction between "return a summary row" and "return only a summary row".

(And no matter what the actual answer might be in the end, if this discussion had happened in the actual interview someone would probably have been hired on the spot. Very Happy )
Re: Count [message #442777 is a reply to message #442645] Wed, 10 February 2010 08:35 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
SUM(total_sales) OVER (PARTITION BY city) AS TOTAL_SALES_PER_CITY


The value returned by the above expression is computed by looking at a set of rows. The value may be relative to some anchor but where the value eventually gets displayed in not relevant to the nature of the aggregating aspect of the analytic.

Indeed, the above expression is equal to this query:

select sum(total_sales)
from myenterprise
where city = :city
;

for each city in the master query.

How can anyone say that an anlalytic which is the quivelant of an aggregate query, is not itself an aggregate?

Kevin
Re: Count [message #442780 is a reply to message #442645] Wed, 10 February 2010 08:37 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
hehe, yeah, or else be made to scared to hire anyone.
Previous Topic: how to combine all fields in one update statement
Next Topic: which execution sequence is maintained in where clause condition ?
Goto Forum:
  


Current Time: Sat Dec 10 08:41:57 CST 2016

Total time taken to generate the page: 0.27197 seconds