Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
How to count the total record of the below query ...

Answered How to count the total record of the below query ...

  • Saturday, February 16, 2013 4:23 AM
     
     
    Sign In to Vote
    0

    Hi, 

    I tried using the Adodb.recordcount but it just returned -1...

    Please advise the sql so I can have a return value of "30" as result-set ... Thanks !!


    • Edited by kkcci88888 Saturday, February 16, 2013 4:25 AM
    •  
     

All Replies

  • Saturday, February 16, 2013 5:40 AM
     
     Proposed Answer Has Code
    Sign In to Vote
    0

    Since you have prism in the SELECT and GROUP BY clauses, then add COUNT(*) to it to get the count=30

    SELECT COUNT(*) --this gives the count as 30
FROM [SCL1111].[dbo].[PrismPerformance]
WHERE PrismANAdate BETWEEN '2012-02-01' AND '2013-02-08'
		AND totalstation='TS0448'
GROUP BY prism


    Narsimha


    • Edited by Naarasimha Saturday, February 16, 2013 6:20 AM
    • Proposed As Answer by Dineshkumar Saturday, February 16, 2013 6:58 AM
    •  
     
  • Saturday, February 16, 2013 6:26 AM
     
     
    Sign In to Vote
    0

    YOU CAN USE LIKE THIS -

    SELECT COUNT(*) 
    FROM [SCL1111].[dbo].[PrismPerformance]
    WHERE PrismANAdate BETWEEN '2012-02-01' AND '2013-02-8'AND totalstation='TS0448'
    GROUP BY prism

    Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

     
  • Saturday, February 16, 2013 11:15 AM
     
     
    Sign In to Vote
    0

    Hi,

    Thanks but this will give the total count of the prism total reading count for the period (e.g 290 occurrences) HOWEVER, I would like the result is the result row count (i,e 30 )...

     
  • Saturday, February 16, 2013 11:56 AM
     
     Proposed Answer Has Code
    Sign In to Vote
    0
     
    SELECT COUNT(*) 
FROM [SCL1111].[dbo].[PrismPerformance]
WHERE PrismANAdate BETWEEN '2012-02-01' AND '2013-02-8'AND totalstation='TS0448'

    try this ..

    Dineshkumar

    • Proposed As Answer by Dineshkumar Sunday, February 17, 2013 2:43 AM
    •  
     
  • Saturday, February 16, 2013 12:03 PM
     
     
    Sign In to Vote
    0

    group by will split the records, the above will return only the total count (ie30 ).  Removing the group by clause from the query produces the right result.

    Thanks

    Dineshkumar

     
  • Saturday, February 16, 2013 12:18 PM
     
      Has Code
    Sign In to Vote
    0

    Hi,

    Is not the below code working ?

    SELECT COUNT(*) 
FROM [SCL1111].[dbo].[PrismPerformance]
WHERE PrismANAdate BETWEEN '2012-02-01' AND '2013-02-8'AND totalstation='TS0448'
Group by Prism

    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

     
  • Saturday, February 16, 2013 2:08 PM
     
     
    Sign In to Vote
    0

    I tried using the Adodb.recordcount but it just returned -1...

    Please advise the sql so I can have a return value of "30" as result-set ... Thanks !!

    The RecordCount is available only after the entire result set is processed.  Make sure your application has processed the results before using the property.

    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

     
  • Saturday, February 16, 2013 4:56 PM
     
     Answered Has Code
    Sign In to Vote
    0

    Try removing the GroupBy clause and do -

    SELECT COUNT(*)
FROM [SCL1111].[dbo].[PrismPerformance]
WHERE PrismANAdate BETWEEN '2012-02-01' AND '2013-02-08'
		AND totalstation='TS0448'

    Or

    If you want to keep that as it is, then try

    SELECT COUNT(*)
FROM
(
 SELECT FirstName
 FROM AdventureWorks2008R2.Person.Person
 WHERE ModifiedDate BETWEEN '2001-01-01' 
   AND '2002-01-01'
 GROUP BY FirstName
)T

    Narsimha

    • Marked As Answer by kkcci88888 Sunday, February 17, 2013 5:11 AM
    •  
     
  • Sunday, February 17, 2013 4:46 AM
    Moderator
     
     
    Sign In to Vote
    0

    Your question is not clear, but try

    select Prism, count(*) over (partition by Prism) as cntGroup from ...

    where ...

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

     
  • Sunday, February 17, 2013 5:12 AM
     
     
    Sign In to Vote
    0

    Thanks, Narsimha ... this statment work as I wanted !! Many thanks .... 

    select count(*)  from (SELECT prism 
      FROM [SCL1111].[dbo].[PrismPerformance] where PrismANAdate between '2013-02-01' and '2013-02-08' and totalstation = 'TS0448' group by Prism) T 

    it return my desired result = 30 

    SPECIFIC hints... Thanks again :)


    • Edited by kkcci88888 Sunday, February 17, 2013 5:12 AM
    •