May 4, 2010 at 1:03 am
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??
May 4, 2010 at 1:09 am
hi,
select @@rowcount will display the number of rows returned from your query.
Regards,
MShenel
May 4, 2010 at 1:28 am
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 4, 2010 at 1:57 am
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!
May 4, 2010 at 3:16 am
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
May 4, 2010 at 6:10 am
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
May 6, 2010 at 10:35 pm
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply