SQL add up rows in a column

Question

I'm running SQL queries in Orion Report Writer for Solarwinds Netflow Traffic Analyzer and am trying to add up data usage for specific conversations coming from the same general sources. In this case it is netflix. I've made some progress with my query.

SELECT  TOP 10000 FlowCorrelation_Source_FlowCorrelation.FullHostname AS Full_Hostname_A,
SUM(NetflowConversationSummary.TotalBytes) AS SUM_of_Bytes_Transferred,
SUM(NetflowConversationSummary.TotalBytes) AS Total_Bytes

FROM 
((NetflowConversationSummary LEFT OUTER JOIN FlowCorrelation FlowCorrelation_Source_FlowCorrelation ON (NetflowConversationSummary.SourceIPSort = FlowCorrelation_Source_FlowCorrelation.IPAddressSort))  LEFT OUTER JOIN FlowCorrelation FlowCorrelation_Dest_FlowCorrelation ON (NetflowConversationSummary.DestIPSort = FlowCorrelation_Dest_FlowCorrelation.IPAddressSort))  INNER JOIN Nodes ON (NetflowConversationSummary.NodeID = Nodes.NodeID)


WHERE 
( DateTime BETWEEN 41539 AND 41570 )
 AND  
(
  (FlowCorrelation_Source_FlowCorrelation.FullHostname LIKE 'ipv4_1.lagg0%')
)

GROUP BY FlowCorrelation_Source_FlowCorrelation.FullHostname, FlowCorrelation_Dest_FlowCorrelation.FullHostname, Nodes.Caption, Nodes.NodeID, FlowCorrelation_Source_FlowCorrelation.IPAddress

So I've got an output that filters everything but netflix sessions (Full_Hostname_A) and their total usage for each session (Sum_Of_Bytes_Transferred)

I want to add up Sum_Of_Bytes_Transferred to get a total usage for all netflix sessions listed, which will output to Total_Bytes. I created the column Total_Bytes, but don't know how to output a total to it.

For some asked clarification, here is the output from the above query:

enter image description here

I want the Total_Bytes Column to be all added up into one number.

I can, yes? Only I know I'm missing something. It isn't working for me. — mateikav, Oct 24 '13 at 16:46
I am unfamiliar with what you are doing, but my thought is: you are getting 10,000 rows with the cols: "Full_Hostname_A", "SUM_of_Bytes_Transferred", and "Total_Bytes" If you want to sum that up further, you can sum( Total_Bytes ) ? — Fallenreaper, Oct 24 '13 at 16:49
@FallenReaper If I SUM(TotalBytes), the output is just a duplicate column displaying the bytes associated with each row. I want to add all the rows up in the column you get from TotalBytes — mateikav, Oct 24 '13 at 17:02
If you did something like: Select hostname, sum(total) from Table group by hostname; that might work. I would do a subselect, but im isure there is a proper Relational Algebraic Join. — Fallenreaper, Oct 24 '13 at 17:06
Please post **sample data** and the **expected result**. Your requirements are somewhat ambiguous. — PM 77-1, Oct 24 '13 at 17:29
edited to include picture of the table. The bottom right window is the table output from the query. — mateikav, Oct 24 '13 at 18:04

score 0 · Answer 1 · answered Oct 25 '13 at 02:41

I have no familiarity with the reporting tool you are using.

From reading your post I'm thinking you want the the first 2 columns of data that you've got, plus at a later point in the report, a single figure being the sum of the total_bytes column you're already producing.

Your reporting tool probably has some means of totalling a column, but you may need to get the support people for the reporting tool to tell you how to do that.

Aside from this, if you can find a way of calling a separate query in a latter section of the report, or if you embed a new report inside your existing report, after the detail section, and use that to run a separate query then you should be able to get the data you want with this:

SELECT Sum(Total_Bytes) as [Total Total Bytes] 
FROM ( yourExistingQuery ) x

yourExistingQuery means the query you've already got, in full (doesnt have to be put on one line), the paretheses are required, and so is the "x". (The latter provides a syntax-required name for the virtual table which your query defines).

Hope this helps.

SQL add up rows in a column

1 Answers1