How to count a query result?

  • Dear All,

    I have a query which I want to count how many records returned by this query.

    The query is the following:

    select distinct O.ID_NUMBER from Table as O

    WHERE (O.FIRST_DATE BETWEEN '20090101' AND '20091231')

    --the previous query return 17 records as in my DB

    I want to count those records and return one record with a value of 17.

    I wrote a query to count like this:

    select COUNT((select distinct O.ID_NUMBER from Table as O

    WHERE (O.FIRST_DATE BETWEEN '20090101' AND '20091231') from Table as O

    BUT IT GIVES ME AN ERROR:

    Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

    How can I Count the 17 record in this case??

  • hi,

    select @@rowcount will display the number of rows returned from your query.

    Regards,
    MShenel

  • I know this,

    But I forgot to tell you that my query return values for more than one condition.

    I mean: the query returns records for more than one person, and I want to count each persons' records in separate.

    The @@ROWCOUNT counts all returned records.

    the following is my query for 2 persons where I want to count the records returned for each:

    select distinct OO.ORDER_NUMBER, COUNT((select distinct OO.ORDER_NUMBER )) from Table as OO

    WHERE (OO.SUBMITTED_DATE BETWEEN '20090101' AND '20091231') AND (OO.TICKET_NUMBER IS NOT NULL) and OO.NIN_CODE='990685711' or OO.NIN_CODE='993435940'

    group by OO.ORDER_NUMBER

    note: The two persons are represented by OO.NIN_CODE

    and so, I want to count each persons records, and this is also a sample for a huge number of persons where I want to count each person data in the previous condition

  • May be, something like this?

    WITH CTE(ID_NUMBER ) as

    (

    select distinct O.ID_NUMBER ID_NUMBER from Table as O

    WHERE (O.FIRST_DATE BETWEEN '20090101' AND '20091231')

    )

    select ID_NUMBER , count(ID_NUMBER) R_Count from CTE

    GROUP BY ID_NUMBER

    Or rather,

    select O.ID_NUMBER , count(O.ID_NUMBER) R_Count from Table as O

    WHERE (O.FIRST_DATE BETWEEN '20090101' AND '20091231')

    GROUP BY O.ID_NUMBER

    Please provide us teh full details, as in , the table schemas, some sample data, and your EXACT desired result. There will be N-number of ways doing what u wanted..

    Hope this helps you!

    Cheers!

  • ColdCoffee (5/4/2010)


    May be, something like this?

    WITH CTE(ID_NUMBER ) as

    (

    select distinct O.ID_NUMBER ID_NUMBER from Table as O

    WHERE (O.FIRST_DATE BETWEEN '20090101' AND '20091231')

    )

    select ID_NUMBER , count(ID_NUMBER) R_Count from CTE

    GROUP BY ID_NUMBER

    I used the previous query for one person like:

    WITH CTE(ID_NUMBER ) as

    (

    select distinct O.ORDER_NUMBER ID_NUMBER from TSORDR as O

    WHERE (O.SUBMITTED_DATE BETWEEN '20090101' AND '20091231') and (O.NIN_CODE='990685711')

    )

    select ID_NUMBER , count(ID_NUMBER) R_Count from CTE

    GROUP BY ID_NUMBER

    But is does not give the Count for all records in one record, it gives me each record or ID_NUMBER Count, and in this case the result was the following (17 record):

    ID_NUMBERR_Count

    016931421

    017158281

    017182761

    017233871

    017261311

    017432801

    017499361

    017514751

    018043581

    018185501

    018344441

    018469651

    018690431

    018825311

    019103981

    019378681

    019750941

    and If I put two persons in the query like:

    WITH CTE(ID_NUMBER ) as

    (

    select distinct O.ORDER_NUMBER ID_NUMBER from TSORDR as O

    WHERE (O.SUBMITTED_DATE BETWEEN '20090101' AND '20091231') and (O.NIN_CODE='990685711' OR O.NIN_CODE='993435940')

    )

    select ID_NUMBER , count(ID_NUMBER) R_Count from CTE

    GROUP BY ID_NUMBER

    then the result will be (32 record) -> 17 record for person 1, and 15 for person 2:

    ID_NUMBERR_Count

    016895791

    016918541

    016931421

    017025411

    017158281

    017170151

    017182761

    017199361

    017199601

    017199641

    017233871

    017261311

    017271901

    017432801

    017499361

    017514751

    018043581

    018185501

    018267041

    018267061

    018344441

    018469651

    018648761

    018648791

    018690431

    018715471

    018825311

    019103981

    019378681

    019750941

    019814151

    019863711

    I want the previous result to be returned in two records for the two persons like:

    ID_NUMBERR_Count

    1 17

    2 15

  • Dear All,

    I solved it, and I will tell you how after finishing the whole query and give the report to my MANAGER, since he is waiting for the report

  • Something like this...

    Select NIN_CODE,Count(*) as Cnt from (

    select distinct OO.ORDER_NUMBER, OO.NIN_CODE

    from Table as OO

    WHERE (OO.SUBMITTED_DATE BETWEEN '20090101' AND '20091231')

    AND (OO.TICKET_NUMBER IS NOT NULL) and OO.NIN_CODE='990685711' or OO.NIN_CODE='993435940'

    ) Main

    group by NIN_CODE

    ----------------------------------------------------------------------------------------------------------------------------------------------------------------------
    Sometimes, winning is not an issue but trying.
    You can check my BLOG
    [font="Arial Black"]here[/font][/url][/right]

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

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