Returning total rows for one column-CodePudding

I have query below that returns performance data for a specified SQL instance. The issue I am having is same textdata are returned for different time frames. I am looking for a way to sum up the same rows of textdata and return total cpu, duration, read, write.

Thank you for your assistance.

SELECT TOP 40 HostName, DatabaseName, ApplicationName,
       LoginName, CPU, TextData,
       Reads, Writes, Duration, StartTime, EndTime
FROM dbo.PerformanceTable 
WHERE  Duration > 10000

CodePudding user response：

Without sample data it's difficult to suggest a solution. If I understood you correctly you want total cpu, duration, read, write for each HostName, DatabaseName, ApplicationName, TextData regardless of their starttime or endtime.

SELECT HostName, DatabaseName, ApplicationName,TextData,
       sum(CPU), sum(Reads), sum(Writes), sum(Duration)
FROM dbo.PerformanceTable 
group by HostName, DatabaseName, ApplicationName,TextData
WHERE  Duration > 10000
order by HostName, DatabaseName, ApplicationName,TextData

Based on your added screenshot I have revised my answer:

SELECT DatabaseName, TextData,
       sum(CPU) total_CPU , sum(Reads) total_Reads, sum(Writes) total_Writes , sum(Duration) total_Duration
FROM dbo.PerformanceTable 
group by DatabaseName, TextData
WHERE  Duration > 10000
order by DatabaseName, TextData

CodePudding user response：

Do you just want aggregation?

SELECT TextData,
       SUM(Reads), SUM(Writes), SUM(Duration)
FROM dbo.PerformanceTable 
WHERE Duration > 10000
GROUP BY TextData;

CodePudding user response：

I have tested with sample data and both ways are giving same answer. Have a look. Used aggregations

CREATE TABLE PerformanceTable
  (
  TextData VARCHAR(MAX)
 , DatabaseName VARCHAR(100)
 ,CPU BIGINT
 ,READS BIGINT
 ,WRITES BIGINT
, Duration Bigint
 )

 INSERT INTO PerformanceTable  (TextData,DatabaseName,CPU, READS,WRITES,Duration) VALUES ('text', 'master', 45632589,2589633,15106,1457889)
 ,('text', 'master', 45678999,4666767,1001,10002)
 ,('text1', 'DatabSeName', 45678999,6778888,45689,10256)
 ,('text1', 'DatabSeName', 546789,6778888,56378,14578)
 ,('text2', 'MsDb', 546789,6778888,457896,478999)

1st way- All other moderators have provided and this should work fine.

 SELECT 
  TextData
 ,SUM(CPU) AS cpu_sum
 ,SUM(READS) AS Reads_sum
 ,SUM(WRITES) AS writes_sum
 ,SUM(Duration) AS duration_sum
FROM PerformanceTable
WHERE Duration >1000
GROUP BY TextData

Another way

SELECT 
*
FROM 
(
 SELECT 
   ROW_NUMBER() OVER( PARTITION BY TextData ORDER BY TextData ) AS row_id
  ,TextData
  ,SUM(CPU) AS cpu_sum
  ,SUM(READS) AS Reads_sum
  ,SUM(WRITES) AS writes_sum
  ,SUM(Duration) AS duration_sum
FROM PerformanceTable
GROUP BY TextData
) AS A WHERE 
A.row_id = 1
AND  A.duration_sum >1000