June 26, 2012 at 9:53 pm
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
MySQLServer 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 TotalAvailableMB
MySQLServer MyApp1 100200 375 ..
MySQLServer MyApp2 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
June 26, 2012 at 10:29 pm
Use GROUP BY on SQL Server and App name fields and SUM the fields you want to add up.
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
June 27, 2012 at 7:05 am
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
🙂
June 27, 2012 at 7:09 am
Have you tried removing dbs.DBAvailMB from the GROUP BY?
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
June 27, 2012 at 8:03 pm
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