Post reply

Any Way to Calculate the Total Sum of All Rows for a Column

  • November 3, 2009 at 9:44 am

    #206227

    Hi Folks

    I am trying to get a total sum of all rows for a column using this sql as an example.

    Is there any way to do it ?

    I would like to get a total of the size at the bottom

    ===============================

    select

    CAST(name as varchar(66)) logical_name,

    database_id,

    size*8/1024 mb_size

    from

    master.sys.master_files

    where

    physical_name NOT LIKE '%.ldf'

    order by

    name;

    go

    ===============================

    Thanks

    Jim

  • November 3, 2009 at 9:50 am

    #1074367

    There is probably a much simpler way of doing this but this is the first thing that came to mind.

    select

    CAST(name as varchar(66)) logical_name,

    database_id,

    size*8/1024 mb_size

    INTO #temp

    from

    master.sys.master_files

    where

    physical_name NOT LIKE '%.ldf'

    UNION ALL

    SELECT 'TOTAL', NULL, NULL

    UPDATE #temp SET mb_size = (SELECT SUM(mb_size) FROM #temp)

    WHERE #temp.logical_name = 'Total'

    SELECT * FROM #temp

    DROP TABLE #temp

  • November 3, 2009 at 10:01 am

    #1074381

    Hi Matt

    Holy Moly........ 🙂

    i will try it

    what is this #temp ?

    is it a temp table ?

    Thanks

    Jim

  • November 3, 2009 at 10:06 am

    #1074386

    Yeah, it is a table that only has session scope. So once you close the query window where you created it, it will be gone.

  • November 3, 2009 at 10:32 am

    #1074404

    Got it..

    thanks

    Jim

  • November 5, 2009 at 11:10 am

    #1075521

    (Deleted due to posting error)

  • November 5, 2009 at 12:35 pm

    #1075558

    select

    database_id,

    CAST(name as varchar(66)) logical_name,

    sum(size*8/1024) mb_size

    from

    master.sys.master_files

    where

    physical_name NOT LIKE '%.ldf'

    group by

    database_id,

    name

    with rollup

    having

    name is not nullor

    database_id is null

    order by

    case when database_id is null then 1 else 0 end,

    database_id,

    name

  • November 5, 2009 at 12:42 pm

    #1075561

    This script gets the file information for every database on a server, and inserts it into temp table that is queried multiple ways to give various levels of analysis of file space usage.

    Get Server Database File Information

    http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89058

  • November 5, 2009 at 12:45 pm

    #1075562

    Thanks Michael

    I will take a look at what you posted

    Jim

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply