Board index » delphi » Get Count(*) and columns in a single query.

Get Count(*) and columns in a single query.

I want to retrieve certain columns and the no of records retrieved from a
table using a SQL query.
For this I tried to run the query:
Select Count(*), Slf_Symbol from SecurityList; But this gives me the
following error:
"Column 'seclist_fixeddata.slf_symbol' is invalid in the select list because
it is not contained in an aggregate function and there is no GROUP BY
clause."

Is there any other way of getting the no records and columns in the same
query. I know that we can use the ADOQuery's "RecordCount" property, but I
do not want to use it and I do not want to run two separate queries either.

Thanks in Advance,
Vikram

 

Re:Get Count(*) and columns in a single query.


In Oracle, you would do this:

select
  'Record Count' as descr,
  count(0) as value
from
    mytable
union all select
  'Column Count' as descr,
  count(0) as value
from
  user_columns
where
  table_name='MYTABLE'

Point being, you want to take two queries (to get the two pieces of data)
and perform a union so that the results are returned in one result set.

Eric

Quote
"Vikram Kulkarni" <vik...@captools.com> wrote in message

news:3d0e6b92$1_1@dnews...
Quote
> I want to retrieve certain columns and the no of records retrieved from a
> table using a SQL query.
> For this I tried to run the query:
> Select Count(*), Slf_Symbol from SecurityList; But this gives me the
> following error:
> "Column 'seclist_fixeddata.slf_symbol' is invalid in the select list
because
> it is not contained in an aggregate function and there is no GROUP BY
> clause."

> Is there any other way of getting the no records and columns in the same
> query. I know that we can use the ADOQuery's "RecordCount" property, but I
> do not want to use it and I do not want to run two separate queries
either.

> Thanks in Advance,
> Vikram

Re:Get Count(*) and columns in a single query.


Quote
"Vikram Kulkarni" <vik...@captools.com> wrote in message

news:3d0e6b92$1_1@dnews...
Quote
> I want to retrieve certain columns and the no of records retrieved from a
> table using a SQL query.
> For this I tried to run the query:
> Select Count(*), Slf_Symbol from SecurityList; But this gives me the
> following error:
> "Column 'seclist_fixeddata.slf_symbol' is invalid in the select list
because
> it is not contained in an aggregate function and there is no GROUP BY
> clause."

> Is there any other way of getting the no records and columns in the same
> query. I know that we can use the ADOQuery's "RecordCount" property, but I
> do not want to use it and I do not want to run two separate queries

either.

 Select
   Slf_Symbol,
   (select Count(*) as TheCount from SecurityList)
from SecurityList

The question is why you want the same number returned on every row? It's a
lot more efficient to only return the count once in a separate query in this
case.

If what you are really trying to do is get the count for each distinct
Slf_Symbol (if Slf_Symbol is not a unique column) then you need to use a
group by:

Select Count(*), Slf_Symbol from SecurityList
group by Slf_Symbol

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
Those who disdain wealth as a worthy goal for an individual or a society
seem not to realize that wealth is the only thing that can prevent
poverty. - Thomas Sowell

Re:Get Count(*) and columns in a single query.


What database and middleware are you using?

  Richard

Quote
"Vikram Kulkarni" <vik...@captools.com> wrote in message

news:3d0e6b92$1_1@dnews...
Quote
> I want to retrieve certain columns and the no of records retrieved from a
> table using a SQL query.

Re:Get Count(*) and columns in a single query.


I think you are right by saying that the count field will be redundant in
every row of the result returned.

So I will run a separate query and get the count.

Thanks for the insight.

Vikram

"Wayne Niddery [TeamB]" <wnidd...@chaffaci.on.ca.com> wrote in message
news:3d0e7fc0$1_1@dnews...

Quote
> "Vikram Kulkarni" <vik...@captools.com> wrote in message
> news:3d0e6b92$1_1@dnews...
> > I want to retrieve certain columns and the no of records retrieved from
a
> > table using a SQL query.
> > For this I tried to run the query:
> > Select Count(*), Slf_Symbol from SecurityList; But this gives me the
> > following error:
> > "Column 'seclist_fixeddata.slf_symbol' is invalid in the select list
> because
> > it is not contained in an aggregate function and there is no GROUP BY
> > clause."

> > Is there any other way of getting the no records and columns in the same
> > query. I know that we can use the ADOQuery's "RecordCount" property, but
I
> > do not want to use it and I do not want to run two separate queries
> either.

>  Select
>    Slf_Symbol,
>    (select Count(*) as TheCount from SecurityList)
> from SecurityList

> The question is why you want the same number returned on every row? It's a
> lot more efficient to only return the count once in a separate query in
this
> case.

> If what you are really trying to do is get the count for each distinct
> Slf_Symbol (if Slf_Symbol is not a unique column) then you need to use a
> group by:

> Select Count(*), Slf_Symbol from SecurityList
> group by Slf_Symbol

> --
> Wayne Niddery (Logic Fundamentals, Inc.)
> RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
> Those who disdain wealth as a worthy goal for an individual or a society
> seem not to realize that wealth is the only thing that can prevent
> poverty. - Thomas Sowell

Re:Get Count(*) and columns in a single query.


I am using SQL Server 7.0 and ADO.

Thanks,
Vikram

"Richard Biffl"
<REMOVETHISPHRASEborlandn...@REMOVETHISPHRASEblacklettersoftware.com> wrote
in message news:3d0ea738$1_1@dnews...

Quote
> What database and middleware are you using?

>   Richard

> "Vikram Kulkarni" <vik...@captools.com> wrote in message
> news:3d0e6b92$1_1@dnews...
> > I want to retrieve certain columns and the no of records retrieved from
a
> > table using a SQL query.

Other Threads