Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to count a query result?


How to count a query result?

Author
Message
obarahmeh
obarahmeh
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 309
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??
shen-dest
shen-dest
SSC Veteran
SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)SSC Veteran (246 reputation)

Group: General Forum Members
Points: 246 Visits: 409
hi,
select @@rowcount will display the number of rows returned from your query.

Regards,
MShenel
obarahmeh
obarahmeh
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 309
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
ColdCoffee
ColdCoffee
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2317 Visits: 5545
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!
obarahmeh
obarahmeh
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 309
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_NUMBER R_Count
01693142 1
01715828 1
01718276 1
01723387 1
01726131 1
01743280 1
01749936 1
01751475 1
01804358 1
01818550 1
01834444 1
01846965 1
01869043 1
01882531 1
01910398 1
01937868 1
01975094 1

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_NUMBER R_Count
01689579 1
01691854 1
01693142 1
01702541 1
01715828 1
01717015 1
01718276 1
01719936 1
01719960 1
01719964 1
01723387 1
01726131 1
01727190 1
01743280 1
01749936 1
01751475 1
01804358 1
01818550 1
01826704 1
01826706 1
01834444 1
01846965 1
01864876 1
01864879 1
01869043 1
01871547 1
01882531 1
01910398 1
01937868 1
01975094 1
01981415 1
01986371 1

I want the previous result to be returned in two records for the two persons like:
ID_NUMBER R_Count
1 17
2 15
obarahmeh
obarahmeh
SSC Rookie
SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)SSC Rookie (41 reputation)

Group: General Forum Members
Points: 41 Visits: 309
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
Atif-ullah Sheikh
Atif-ullah Sheikh
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3280 Visits: 5159
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 here


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search