how to sum up the rows into one distict row per specific column

  • I have a question - how to sum up the rows into one distict row per specific column:

    so, I have a query that returnes a resultset like this:

    SQLServer MyApp AllocatedMB UsedMB TotalAvailableMB

    MySQLServerMyApp1200 10 ..

    MySQLServerMyApp1 100000 365 ..

    MySQLServerMyApp2 106000 152 ..

    MySQLServerMyApp2 100 212 ..

    What I want is to aggregate per MyApp, and kind of sum up records into one record per MyApp, like this:

    SQLServer MyApp AllocatedMB UsedMB TotalAvailableMB

    MySQLServerMyApp1100200 375 ..

    MySQLServerMyApp2 106100 364 ..

    so that there are only distinct records per MyApp

    current Query:

    with c as

    (

    select Distinct v.SQLServer, v.dbnamee, WA.MyApp, max(v.dt) as dt, v.DBTotMB, v.DBUsedMB, v.DBAvailMB

    ,SUM(v.DBAvailMB) OVER(PARTITION BY v.SQLServer) AS SQLAvailMB

    from v_dbspace as v

    join MyApp as WA

    on v.dbname = WA.dbname

    where v.SQLServer = 'sqlInstance1'

    and v.dt > getdate() - 1

    group by v.SQLServer, v.dbname, WA.MyApp, v.DBAvailMB, v.dt, v.DBTotAlctMB, v.DBUsedMB

    )

    select dbS.SQLServer AS [SQLServer]

    ,dbs.MyApp

    ,sum(DBS.DBTotAlctMB) AS [AllocatedMB]

    ,sum(DBS.DBUsedMB) AS [UsedMB]

    ,sum(DBS.DBAvailMB) AS [TotalAvailableMB]

    from c as dbs

    join c as dbs2

    on dbs2.SQLServer = dbs.SQLServer

    and dbs2.DBAvailMB >= dbs.DBAvailMB

    and (dbs2.DBAvailMB > dbs.DBAvailMB or dbs2.MyApp <= dbs.MyApp)

    group by dbs.SQLServer, dbs.MyApp, dbs.DBAvailMB

    order by dbs.SQLServer, dbs.MyApp, dbs.DBAvailMB desc

  • Use GROUP BY on SQL Server and App name fields and SUM the fields you want to add up.


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • I already do in both the anchor query and the final query in CTE. It doesn't retutn the single individual rows per app though it should...that's basically my question.

    with c as

    (

    select Distinct v.SQLServer, v.dbnamee, WA.MyApp, max(v.dt) as dt, v.DBTotMB, v.DBUsedMB, v.DBAvailMB

    ,SUM(v.DBAvailMB) OVER(PARTITION BY v.SQLServer) AS SQLAvailMB

    from v_dbspace as v

    join MyApp as WA

    on v.dbname = WA.dbname

    where v.SQLServer = 'sqlInstance1'

    and v.dt > getdate() - 1

    group by v.SQLServer, v.dbname, WA.MyApp, v.DBAvailMB, v.dt, v.DBTotAlctMB, v.DBUsedMB

    )

    select dbS.SQLServer AS [SQLServer]

    ,dbs.MyApp

    ,sum(DBS.DBTotAlctMB) AS [AllocatedMB]

    ,sum(DBS.DBUsedMB) AS [UsedMB]

    ,sum(DBS.DBAvailMB) AS [TotalAvailableMB]

    from c as dbs

    join c as dbs2

    on dbs2.SQLServer = dbs.SQLServer

    and dbs2.DBAvailMB >= dbs.DBAvailMB

    and (dbs2.DBAvailMB > dbs.DBAvailMB or dbs2.MyApp <= dbs.MyApp)

    group by dbs.SQLServer, dbs.MyApp, dbs.DBAvailMB

    order by dbs.SQLServer, dbs.MyApp, dbs.DBAvailMB desc

    🙂

  • Have you tried removing dbs.DBAvailMB from the GROUP BY?


    My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]

    My thought question: Have you ever been told that your query runs too fast?

    My advice:
    INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
    The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

    Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
    Since random numbers are too important to be left to chance, let's generate some![/url]
    Learn to understand recursive CTEs by example.[/url]
    [url url=http://www.sqlservercentral.com/articles/St

  • thank you thank you, just what i needed!

Viewing 5 posts - 1 through 4 (of 4 total)

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