Re: counting records in select query

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Konrad (konrad007_at_poczta.onet.pl)
Date: 02/01/05 

Date: Tue, 1 Feb 2005 06:29:05 +0100

My query to counting records looks like:
"SELECT COUNT( DISTINCT Means.Name) FROM Doc_items INNER JOIN Doc_headers ON
Doc_items.Document = Doc_headers.ID INNER JOIN Means ON Doc_items.Means =
Means.ID WHERE (Doc_headers.Doc_type LIKE 'S') "

oryginal query looks like:

SELECT SUM(Doc_items.Quantity),SUM(Doc_items.Value),Means.Unit,Means.Name
FROM Doc_items INNER JOIN Means ON Doc_items.Means = Means.ID INNER JOIN "+

"Doc_headers ON Doc_items.Document = Doc_headers.ID WHERE
(Doc_headers.Doc_type LIKE 'S') GROUP BY Means.Unit,Means.Name"

The query counting records is not accurate because it doesn't take itno
account Means.Unit but only Means.Name.

Really Means.Name is much bigger than Means.Unit so results are no t so bad.

But how to know what number of records will be returned from secon query?

Using Count(*) gives numer o records form all resulting rows separately.

I want sum of this.

Best regards

Konrad

"William (Bill) Vaughn" <billvaRemoveThis@nwlink.com> wrote in message
news:eqNbsUACFHA.3824@TK2MSFTNGP10.phx.gbl...
> Yes, but the count might not be particularly accurate and it will hurt
> performance.
> You can execute a SELECT COUNT(*) with the appropriate WHERE clause to
> return a count of the rows that qualify for the rowset. However, this
takes
> almost the same amount of time (especially for complex queries) to run the
> query. This means it will cost nearly twice as much as executing the query
> without counting the rows first. In addition, since the number of member
> rows could change from the time you count to the time you query, the count
> could be off--way off. For simple queries counting is fine--but it just
> hurts scalability.
>
> hth
>
> --
> ____________________________________
> William (Bill) Vaughn
> Author, Mentor, Consultant
> Microsoft MVP
> www.betav.com
> Please reply only to the newsgroup so that others can benefit.
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> __________________________________
>
> "Konrad" <konrad007@poczta.onet.pl> wrote in message
> news:OtftJu2BFHA.2016@TK2MSFTNGP15.phx.gbl...
> > Iterate through all records isn't big problem in my case
> > because number of records returned is about to 1000.
> > And as I see it takes lower than 1 sec.
> > But I'am using SQL Server over internet and sending
> > this records is bigger problem. So I want to know
> > how many records is to send and does this operation should
> > be completed or not.
> > Do you know how count number of records to send
> > having select query?
> >
> > Best regards
> > Konrad
> >
> >
> > "Miha Markic [MVP C#]" <miha at rthand com> wrote in message
> > news:e6UA0ewBFHA.2428@TK2MSFTNGP14.phx.gbl...
> >> Hi,
> >>
> >> This not a good practice. Counting records has to iterate through all
> >> records on server and you'll probably have to run one select for
counting
> >> and the other for returning the actual records.
> >> Why don't you simply limit the select with TOP (or similar) clause?
> >>
> >> --
> >> Miha Markic [MVP C#] - RightHand .NET consulting & development
> >> SLODUG - Slovene Developer Users Group
> >> www.rthand.com
> >>
> >> "Konrad" <konrad007@poczta.onet.pl> wrote in message
> >> news:OCshCLwBFHA.2196@TK2MSFTNGP14.phx.gbl...
> >> > Hi
> >> >
> >> > I have complicated select query
> >> > how to get prior number of records
> >> > returned from this query.
> >> > One number, to decide
> >> > if records should be loaded.
> >> >
> >> > Thanks
> >> > Konrad
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Relevant Pages

  • Re: correlated subquery in the crosstab
    ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... I have students, courses, exam groups containing exams of courses, ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... I solved my problem using stored queries to act as subqueries. ... FROM (tblStudent INNER JOIN (tblClass INNER JOIN ... values from the outer query and I have made an alias for it. ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: correlated subquery in the crosstab
    ... The first query ... TRANSFORM FirstAS FirstOfscore ... FROM tblStudent INNER JOIN (((tblEduYear INNER JOIN tblExamGrp ON ... that the crosstab query in access has much more capablities than ...
    (microsoft.public.access.queries)
  • Re: Matching records for an update query
    ... then the update query would look something like ... UPDATE RegisteredMembers INNER JOIN BusinessChanges ...
    (microsoft.public.access.queries)
  • Re: Ranking query
    ... I expect that using a named query (as opposed to its SQL) will be OK. ... INNER JOIN qryRepairs AS I2 ...
    (microsoft.public.access.queries)