Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ProgressBoy
Starting Member
14 Posts |
 Posted - 2012-06-26 : 23:58:34
|
| 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 TotalAvailableMBMySQLServer MyApp1 200 10 ..MySQLServer MyApp1 100000 365 ..MySQLServer MyApp2 106000 152 ..MySQLServer MyApp2 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 TotalAvailableMBMySQLServer MyApp1 100200 375 ..MySQLServer MyApp2 106100 364 ..so that there are only distinct records per MyAppcurrent 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() - 1group 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 |
|
|
|
|
|
|
|
how to sum up the rows into one distict row per sp