Post reply

Query that returns SUM of column based on other data in other column

  • August 14, 2014 at 11:34 am

    #298109

    I have what I thought was a simple query but turning into a bit of a mess...

    I have a table:

    CREATE TABLE [dbo].[TempDBFREESpace](

    [ServerName] [varchar](100) NOT NULL,

    [DatabaseName] [varchar](250) NOT NULL,

    [FileType] [varchar](10) NOT NULL,

    [FreeSpace] [decimal](15, 2) NOT NULL

    ) ON [PRIMARY]

    GO

    INSERT INTO TempDBFREESpace(ServerName, DatabaseName ,FileType ,FreeSpace)

    VALUES ('SERVER1','db1','DATA',12986.08)

    ,('SERVER1','db1','LOG',648.31)

    ,('SERVER1','db2','DATA',1658726.32)

    ,('SERVER1','db2','LOG',2356789.84)

    ,('SERVER2','db1','DATA',163.23)

    ,('SERVER2','db1','LOG',447.17)

    ,('SERVER2','db2','DATA',96.12)

    ,('SERVER2','db2','LOG',200.01)

    ,('SERVER3','db1','DATA',2.98)

    ,('SERVER3','db1','LOG',77.1)

    ,('SERVER3','db2','DATA',546795.44)

    ,('SERVER3','db2','LOG',254698.67)

    GO

    I run this:

    SELECT ServerName

    ,SUM(FreeSpace) AS 'Available Space'

    ,FileType

    FROM TempDBFREESpace

    GROUP BY ServerName, FileType

    ORDER BY 2 DESC

    Which returns:

    ServerName Available Space FileType

    SERVER1 2357438.15 LOG

    SERVER1 1671712.40 DATA

    SERVER3 546798.42 DATA

    SERVER3 254775.77 LOG

    SERVER2 647.18 LOG

    SERVER2 259.35 DATA

    I would like it to return:

    ServerName Available DATA Space Available LOG Space

    SERVER1 1671712.40 2357438.15

    SERVER3 546798.42 254775.77

    SERVER2 259.35 647.18

    Sorry about the formatting.

    Not sure how to do tables...

    Any help appreciated.

  • August 14, 2014 at 11:37 am

    #1738156

    Something like this:

    SELECT ServerName

    ,SUM(CASE WHEN FileType <> 'LOG' THEN FreeSpace ELSE 0 END) AS 'Available Data Space'

    ,SUM(CASE WHEN FileType = 'LOG' THEN FreeSpace ELSE 0 END) AS 'Available Log Space'

    FROM TempDBFREESpace

    GROUP BY ServerName

    ORDER BY 1 DESC

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • August 14, 2014 at 11:42 am

    #1738160

    Perfect.

    Thank you so much.

Viewing 3 posts - 1 through 2 (of 2 total)

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