none
I want to modify this query to get the rows count

    Question

  • -- I want to modify this query to get the rows count , Please assist

    DECLARE @dCurrentTime DATETIME

    DECLARE @dCurrentTimeMinus5 DATETIME

     

    SET @dCurrentTime = GETDATE()

    SET @dCurrentTimeMinus5 = DATEADD(minute, -5, @dCurrentTime)

     

    --Return lIDS for only sessions last accessed in the last 5 minutes

    SELECT lID, MAX(dLastAccessed)

    FROM SessionState_Variables

    WHERE dLastAccessed BETWEEN @dCurrentTimeMinus5 AND @dCurrentTime

    GROUP BY lID

    ORDER BY 2 DESC


    k

    Wednesday, July 03, 2013 9:15 PM

Answers

  • SELECT Count(*) As NumberRows

    FROM (

        SELECT lID

        FROM SessionState_Variables

        WHERE dLastAccessed BETWEEN @dCurrentTimeMinus5 AND @dCurrentTime

        GROUP BY lID

    ) As SessionsTable

    Carlos.


    Thursday, July 04, 2013 11:31 AM
  • Can you post your current code so we can correct it?

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


    My blog


    My TechNet articles

    • Marked as answer by '''HuuM''' Friday, July 05, 2013 10:54 PM
    Thursday, July 04, 2013 6:13 PM

All replies

  • You mean the count of rows for each lID?  Then:

    --Return lIDS for only sessions last accessed in the last 5 minutes
    
    SELECT lID, MAX(dLastAccessed), COUNT(*)
    FROM SessionState_Variables
    WHERE dLastAccessed BETWEEN @dCurrentTimeMinus5 AND @dCurrentTime
    GROUP BY lID
    ORDER BY 2 DESC

    Wednesday, July 03, 2013 10:16 PM
  • Giving column positions in ORDER BY is a bad practice, instead give column names.

    SELECT lID, MAX(dLastAccessed) max_lastaccessed,count(1) as row_cnt
    FROM SessionState_Variables
    WHERE dLastAccessed BETWEEN @dCurrentTimeMinus5 AND @dCurrentTime
    GROUP BY lID
    ORDER BY max_lastaccessed DESC


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, July 03, 2013 11:39 PM
  • Thanks Sarat,

    Is there any other way to write this query?

    thanks


    k

    Thursday, July 04, 2013 12:17 AM
  • I want to modify to
    return row count for this query

    DECLARE @dCurrentTimeDATETIME

    DECLARE @dCurrentTimeMinus5DATETIME

    SET @dCurrentTime= GETDATE()

    SET @dCurrentTimeMinus5= DATEADD(minute,-5, @dCurrentTime)

    --Return lIDS for only sessions last accessed in the last 5 minutes

    SELECT lID,MAX(dLastAccessed)

    FROM SessionState_Variables

    WHERE dLastAccessedBETWEEN @dCurrentTimeMinus5 AND @dCurrentTime

    GROUPBY lID

    ORDERBY 2 DESC


    k

    Thursday, July 04, 2013 12:22 AM
  • Your question is unclear, but check @@ROWCOUNT variable right after the query

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


    My blog


    My TechNet articles

    Thursday, July 04, 2013 12:24 AM
  • HI Naomi,

    we use this query to get the details of users who logged in in alst 5 minutes,

    now we only need a count of number of users thats it.

    Please assist


    k

    Thursday, July 04, 2013 12:33 AM
  • Your question is unclear, but check @@ROWCOUNT variable right after the query

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


    My blog


    My TechNet articles

    You cannot vote on your own post
    0

    I want to modify this query to
    return row count

    DECLARE @dCurrentTimeDATETIME

    DECLARE @dCurrentTimeMinus5DATETIME

    SET @dCurrentTime=GETDATE()

    SET @dCurrentTimeMinus5=DATEADD(minute,-5, @dCurrentTime)

    --Return lIDS for only sessions last accessed in the last 5 minutes

    SELECT lID,MAX(dLastAccessed)

    FROM SessionState_Variables

    WHERE dLastAccessedBETWEEN @dCurrentTimeMinus5AND @dCurrentTime

    GROUPBY lID

    ORDERBY 2DESC



    k


    • Edited by '''HuuM''' Thursday, July 04, 2013 1:05 AM becasue its urgent
    Thursday, July 04, 2013 12:47 AM
  • You cannot vote on your own post
    0

    I want to modify to
    return row count for this query

    DECLARE @dCurrentTimeDATETIME

    DECLARE @dCurrentTimeMinus5DATETIME

    SET @dCurrentTime=GETDATE()

    SET @dCurrentTimeMinus5=DATEADD(minute,-5, @dCurrentTime)

    --Return lIDS for only sessions last accessed in the last 5 minutes

    SELECT lID,MAX(dLastAccessed)

    FROM SessionState_Variables

    WHERE dLastAccessedBETWEEN @dCurrentTimeMinus5AND @dCurrentTime

    GROUPBY lID

    ORDERBY 2DESC


    k

    Thursday, July 04, 2013 1:03 AM
  • Thanks Sarat,

    Is there any other way to write this query?

    thanks


    k

    If IID column signifies users ,then use the option said by Naomi.

    Write this immediately after the original query.

    Declare @count int

    set @count =@@ROWCOUNT 


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, July 04, 2013 1:20 AM
  • Creating multiple threads for the same question may loose focus from answerers..

    track the issue in a single thread, http://social.msdn.microsoft.com/Forums/en-US/05903793-dacb-424c-a274-636373909a53/i-want-to-modify-this-query-to-get-the-rows-count


    Thanks
    Sarat

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Thursday, July 04, 2013 1:23 AM
  • Can you please write it down for me i think i am making some mistake.

    Regards


    k

    Thursday, July 04, 2013 3:39 AM
  • DECLARE @dCurrentTimeDATETIME

    DECLARE @dCurrentTimeMinus5DATETIME

    SET @dCurrentTime= GETDATE()

    SET @dCurrentTimeMinus5= DATEADD(minute,-5, @dCurrentTime)

    --Return lIDS for only sessions last accessed in the last 5 minutes

    SELECT lID,MAX(dLastAccessed),COUNT(*) OVER () row_count

    FROM SessionState_Variables

    WHERE dLastAccessedBETWEEN @dCurrentTimeMinus5 AND@dCurrentTime

    GROUPBY lID

    ORDERBY 2 DESC


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Thursday, July 04, 2013 5:16 AM
  • DECLARE @dCurrentTimeDATETIME DECLARE @dCurrentTimeMinus5DATETIME SET @dCurrentTime=GETDATE() SET @dCurrentTimeMinus5=DATEADD(minute,-5, @dCurrentTime) --Return lIDS for only sessions last accessed in the last 5 minutes SELECT lID,MAX(dLastAccessed) FROM SessionState_Variables WHERE dLastAccessedBETWEEN @dCurrentTimeMinus5AND @dCurrentTime GROUPBY lID ORDERBY 2DESC

    Declare @count int

    set @count =@@ROWCOUNT 


    • Marked as answer by '''HuuM''' Thursday, July 04, 2013 1:40 PM
    • Unmarked as answer by '''HuuM''' Thursday, July 04, 2013 1:40 PM
    Thursday, July 04, 2013 10:06 AM
  • SELECT Count(*) As NumberRows

    FROM (

        SELECT lID

        FROM SessionState_Variables

        WHERE dLastAccessed BETWEEN @dCurrentTimeMinus5 AND @dCurrentTime

        GROUP BY lID

    ) As SessionsTable

    Carlos.


    Thursday, July 04, 2013 11:31 AM
  • I am getting this error

    Column 'SessionState_Variables.lID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


    k

    Thursday, July 04, 2013 5:47 PM
  • Can you post your current code so we can correct it?

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


    My blog


    My TechNet articles

    • Marked as answer by '''HuuM''' Friday, July 05, 2013 10:54 PM
    Thursday, July 04, 2013 6:13 PM
  • thanks its resolved

    k

    Friday, July 05, 2013 10:54 PM