Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to count a query result? Expand / Collapse
Author
Message
Posted Tuesday, May 4, 2010 1:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 6:01 AM
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??
Post #915103
Posted Tuesday, May 4, 2010 1:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, December 14, 2012 2:23 AM
Points: 244, Visits: 409
hi,
select @@rowcount will display the number of rows returned from your query.


Regards,
MShenel
Post #915104
Posted Tuesday, May 4, 2010 1:28 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 6:01 AM
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
Post #915111
Posted Tuesday, May 4, 2010 1:57 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, December 11, 2014 5:03 PM
Points: 2,262, Visits: 5,427
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!
Post #915128
Posted Tuesday, May 4, 2010 3:16 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 6:01 AM
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
Post #915153
Posted Tuesday, May 4, 2010 6:10 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 31, 2012 6:01 AM
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
Post #915222
Posted Thursday, May 6, 2010 10:35 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Saturday, November 29, 2014 3:55 PM
Points: 3,244, Visits: 5,010
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

Post #917598
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse