Rowcount

11 Aug 2006 5:56 PM

Rob

SQL newbii here. I am working with Reporting Services with an Oracle DB. I
want to know how to determine how many rows are returned in a query. For
example:

select *
from company

How do I get a parameter or something that shows me how many rows the query
returned?

I have seen several posts with reference to @@rowcount; but nothing seems to
work. It might be because of the Oracle DB and not liking @ ; but since I am
new to this, I just don't know.

I have also seen stuff like

select count(*)
from (select * from company)

but that does not work either..... any clues out there.

Thanks
Rob

11 Aug 2006 6:04 PM

Ole Kristian Bangås

=?Utf-8?B?Um9i?= <R**@discussions.microsoft.com> wrote in

news:357287FA-7B8C-4899-B4A2-3FEAF2405706@microsoft.com:

> I have also seen stuff like
>
> select count(*)
> from (select * from company)
>
> but that does not work either..... any clues out there.

Perhaps you've seen this?

SELECT count(*) FROM company;

--
Ole Kristian Bangås
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP

11 Aug 2006 6:40 PM

Rob

Thanks guys for the quick response; but I guess my simple example was too
simple.

If my query is:

SELECT s.network_id, ms.mso_num, m.mso_name
FROM service s, mso_service ms, mso m
WHERE s.service_id = ms.service_id
and ms.mso_num = m.mso_num
and s.network_id = 1006
GROUP BY s.network_id, ms.mso_num, m.mso_name
ORDER BY ms.mso_num

how do I get the count of the number of rows this query returns?

I tried adding the count(*) to the end of the select statement and it
returns a count for each instance but not a total of number of rows.

I have a feeling I am looking for something like:

select count(*)
from (select.......)

but I cannot get it to work.

Show quote

"Ole Kristian BangÃ¥s" wrote:

> =?Utf-8?B?Um9i?= <R**@discussions.microsoft.com> wrote in
> news:357287FA-7B8C-4899-B4A2-3FEAF2405706@microsoft.com:
>
> > I have also seen stuff like
> >
> > select count(*)
> > from (select * from company)
> >
> > but that does not work either..... any clues out there.
>
> Perhaps you've seen this?
>
> SELECT count(*) FROM company;
>
> --
> Ole Kristian BangÃ¥s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP
>

11 Aug 2006 6:43 PM

Rob

AHHH HAA, I think I got it.

select count(*)
from (select....)x

I was forgetting the "x" at the end of the query (no wonder I was getting
the sql error). I don't know if this is a SQL thing or an Oracle thing; but
the above worked.

Thanks so much for your input, it jogged my mind.

Rob

Show quote

"Ole Kristian BangÃ¥s" wrote:

> =?Utf-8?B?Um9i?= <R**@discussions.microsoft.com> wrote in
> news:357287FA-7B8C-4899-B4A2-3FEAF2405706@microsoft.com:
>
> > I have also seen stuff like
> >
> > select count(*)
> > from (select * from company)
> >
> > but that does not work either..... any clues out there.
>
> Perhaps you've seen this?
>
> SELECT count(*) FROM company;
>
> --
> Ole Kristian BangÃ¥s
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging, MCTS, MCITP
>

11 Aug 2006 6:07 PM

glen

select <fieldname1>,<fieldname2>, count(*)
from company

that oughta do it

--
glen

Show quote

"Rob" wrote:

> SQL newbii here. I am working with Reporting Services with an Oracle DB. I
> want to know how to determine how many rows are returned in a query. For
> example:
>
> select *
> from company
>
> How do I get a parameter or something that shows me how many rows the query
> returned?
>
> I have seen several posts with reference to @@rowcount; but nothing seems to
> work. It might be because of the Oracle DB and not liking @ ; but since I am
> new to this, I just don't know.
>
> I have also seen stuff like
>
> select count(*)
> from (select * from company)
>
> but that does not work either..... any clues out there.
>
> Thanks
> Rob

11 Aug 2006 6:33 PM

Tibor Karaszi

> that oughta do it

It did in version 4.21 and earlier. But as of 6.5, SQL Server became ANSI SQL compliant regarding
this. You cannot aggregate and refer to columns alone, unless you also group by over those columns.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/

Show quote

"glen" <g***@discussions.microsoft.com> wrote in message
news:7BBD65F7-262C-45FD-A7D1-8354C3E87087@microsoft.com...
> select <fieldname1>,<fieldname2>, count(*)
> from company
>
> that oughta do it
> --
> glen
>
>
> "Rob" wrote:
>
>> SQL newbii here. I am working with Reporting Services with an Oracle DB. I
>> want to know how to determine how many rows are returned in a query. For
>> example:
>>
>> select *
>> from company
>>
>> How do I get a parameter or something that shows me how many rows the query
>> returned?
>>
>> I have seen several posts with reference to @@rowcount; but nothing seems to
>> work. It might be because of the Oracle DB and not liking @ ; but since I am
>> new to this, I just don't know.
>>
>> I have also seen stuff like
>>
>> select count(*)
>> from (select * from company)
>>
>> but that does not work either..... any clues out there.
>>
>> Thanks
>> Rob