none
Nested functions, Sum of Last(mailboxcount)

    Question

  • Hi,

    I'm using a query whose output is based on Last(mailboxcount) & it is as follows:

    Server Mailbox Count
    ABCD 2345 Based on LAST(mailboxcount)
    EFGH 2234 Based on LAST(mailboxcount)
    IJKL 1123 Based on LAST(mailboxcount)
    TOTAL ???

    How do I sum the above values?

    I'm a newbie in SQL, please help.

    Saturday, June 07, 2014 2:25 PM

Answers

  • I managed to fix it...........

    As a workaround,

    Instead of collecting samples for a date range & then selecting LAST() count, I simply collected the samples only for the Last date & did a SUM of that.

    • Marked as answer by Ijaz Kazi Thursday, June 12, 2014 5:06 PM
    Thursday, June 12, 2014 5:06 PM

All replies

  • select sum(Mailbox Count)
    from YourTable


    T-SQL e-book by TechNet Wiki Community
    My Blog
    My Articles

    Saturday, June 07, 2014 2:32 PM
  • Are you looking for the below?

    create Table t1(Col1 varchar(10),col2 int)
    Insert into t1 values('ABCD',2345),
    ('EFGH',2234),
    ('IJKL',1123)
    
    Select Col1,SUM(Col2) From t1
    Group by Rollup(col1)
    
    Drop table t1

    Saturday, June 07, 2014 3:58 PM
  • i think this

    SELECT *,SUM(MailboxCount) OVER () AS Total
    FROM
    (
    --Your existing query to get last mailbox values
    )t
    

    or if you want it as a new row use this

    ;With CTE
    AS
    (
    --Your existing query to get last mailbox values
    )
    
    SELECT [Server],MailboxCount,1 AS Ord
    FROM CTE
    UNION ALL
    SELECT 'Total',SUM(MailboxCount),2
    FROM CTE
    ORDER BY Ord,[Server]


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, June 07, 2014 6:26 PM
  • To Add more info... 

    In the above example each server (eg ABCD) has many samples of mailbox count value from which I'm selecting the LAST(mailboxcount). Similar for other servers.

    So I'm looking for something like SUM(LAST(mailboxcount))

    Saturday, June 07, 2014 6:28 PM
  • Ok here you go with that

    SELECT SUM(MailboxCount) AS Total
    FROM
    (
    SELECT [Server],MailboxCount,ROW_NUMBER() OVER (PARTITION BY [Server] ORDER BY [Server]) AS Seq
    FROM YourTable
    )t
    WHERE Seq=1
    


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Saturday, June 07, 2014 6:33 PM
  • To Add more info... 

    In the above example each server (eg ABCD) has many samples of mailbox count value from which I'm selecting the LAST(mailboxcount). Similar for other servers.

    So I'm looking for something like SUM(LAST(mailboxcount))

    Ijaz,

    LAST(mailboxcount) - How do you find the Last value of mailboxcount? Is there any date column to identify the last value? Or the highest mailboxcount value is the last count (may be a cumulative count)? If you can find that criteria column, you may need to use in the below script:

    create Table t1(Servername varchar(10),Mailboxcount int) Insert into t1 values('ABCD',2345), ('EFGH',2234), ('IJKL',1123), ('ABCD',2435) ;with cte as ( Select *,ROW_NUMBER()Over(partition by servername

    order by Mailboxcount desc --here you should use the criteria )Rn From t1) Select servername,SUM(Mailboxcount) From cte where Rn=1 Group by Rollup(servername) Drop table t1


    Sunday, June 08, 2014 2:17 AM
  • Hmm there is no LAST function in SQL Server (are you using ACCESS?), if you use SQL Server 2012 there is a new function called LAST_VALUE but still you cannot use nested aggregate functions..

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

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, June 08, 2014 6:10 AM
  • My sincere apologies for the confusion, here is my code:

    select DateTime, Servername, Mailboxcount
    from Perf.vPerfhourly
    where CounterName = 'Mailbox Count' 

    I'm using this in SSRS & selecting LAST(mailboxcount) which gives output as :

    Datetime Servername Mailbox Count
    6/8/2014 ABCD 2345 Based on LAST(mailboxcount)
    6/9/2014 EFGH 2234 Based on LAST(mailboxcount)
    6/10/2014 IJKL 1123 Based on LAST(mailboxcount)
    TOTAL                ???

    Total should be SUM of above values.

    Hope this clears the picture....

    Sunday, June 08, 2014 2:28 PM
  • Did you try our suggestions? Any issues?
    Sunday, June 08, 2014 2:42 PM
  • My sincere apologies for the confusion, here is my code:

    select DateTime, Servername, Mailboxcount
    from Perf.vPerfhourly
    where CounterName = 'Mailbox Count' 

    I'm using this in SSRS & selecting LAST(mailboxcount) which gives output as :

    Datetime Servername Mailbox Count
    6/8/2014 ABCD 2345 Based on LAST(mailboxcount)
    6/9/2014 EFGH 2234 Based on LAST(mailboxcount)
    6/10/2014 IJKL 1123 Based on LAST(mailboxcount)
    TOTAL                ???

    Total should be SUM of above values.

    Hope this clears the picture....

    what happened when you tried my earlier suggestion?

    ie

    SELECT SUM(MailboxCount) AS Total
    FROM
    (
    SELECT [Server],MailboxCount,ROW_NUMBER() OVER (PARTITION BY [Server] ORDER BY [Server]) AS Seq
    FROM YourTable
    )t
    WHERE Seq=1


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, June 08, 2014 2:56 PM
  • As i said in the beginning, each server has multiple samples for a given date range from which I'm picking the LAST() using SSRS.

    If I do a SUM of all the samples it would be incorrect.

    I believe this requires a nested aggregate function in SSRS for TOTAL which should be something like

     SUM(LAST(mailboxcount))

     
    Monday, June 09, 2014 5:00 AM
  • I managed to fix it...........

    As a workaround,

    Instead of collecting samples for a date range & then selecting LAST() count, I simply collected the samples only for the Last date & did a SUM of that.

    • Marked as answer by Ijaz Kazi Thursday, June 12, 2014 5:06 PM
    Thursday, June 12, 2014 5:06 PM