Post reply

without using count function

  • February 7, 2009 at 7:17 pm

    #134524

    Hi all

    is there a way to retrieve records based on count condition but without using count function.

    For e.g.,

    Select X, A, count(B) as cnt From Table

    Group by X, A

    Having count(B) >2

    Thanks in advance

  • February 7, 2009 at 7:33 pm

    #940088

    you need to select from the master table, and include the group as a sub select there somewhere, ie

    SELECT *

    FROM SOMETABLE

    INNER JOIN (Select X, A, count(B) as cnt

    From Table

    Group by X, A

    Having count(B) >2) MYSUBSLECT

    ON SOMETABLE.X = MYSUBSELECT.X

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 7, 2009 at 7:36 pm

    #940090

    thanks lowell,

    What I mean to say is I dont want to use count function.

  • February 7, 2009 at 8:24 pm

    #940094

    you'll need to explain exactly what you want.

    how can you have data BASED on a count if you don't USE a count?

    give us the REAL table with sample data, and show us what you want.

    pseudo code and fake table names obviously took us down the wrong path and do not describe what you want at all.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 7, 2009 at 10:28 pm

    #940099

    I would question why you are wanting to get count something without wanting to use the count(*) function. This is a standard function in the SQL language and is the most optimized way to count something within the system.

    The only reason I don't use a count function is when I want to get a running total. If this is the case I use a CTE.

    Fraggle

  • February 9, 2009 at 3:56 am

    #940363

    Maybe:

    SELECT rows FROM sysindexes WHERE id = object_id ('Table_name') AND indid in (0,1)

  • February 9, 2009 at 4:09 am

    #940368

    Hi,

    you could use the @@rowcount property for getting the number of rows of a table and store in some variable to be used in some query.

    eg:

    select * from tableName;

    select @@ROWCOUNT;

    Mayur

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply