SQL Server: How to add multiple values from multiple counters

Multi tool use
Multi tool use












-1















I have different counters named Available Bytes, Committed Bytes, Modified Page List Bytes with different values. I want to add the values from these counters and all the values are under CounterValue.



-----------------------------------------------------------------------------
Machine Name | Counter Name | Counter ID | CounterDateTime | Counter Value
------------------------------------------------------------------------------
Machine 1 |Available Bytes | 1 | Dec 31st | 123
Machine 1 |Committed Bytes | 2 | Dec 31st | 223
Machine 1 |Modified Bytes | 3 | Dec 31st | 345
Machine 2 |Available Bytes | 4 | Dec 31st | 467
Machine 2 |Committed Bytes | 5 | Dec 31st | 567
Machine 2 |Modified Bytes | 6 | Dec 31st | 678
Machine 3 |Available Bytes | 7 | Dec 31st | 467
Machine 3 |Committed Bytes | 8 | Dec 31st | 567
Machine 3 |Modified Bytes | 9 | Dec 31st | 678


How do I go about doing this?
counter



For instance, I want to do this:



1-[(Available Bytes)/(Available Byes + Committed Bytes + Modified Page List Bytes)] x 100 for each machine name



But I also want to keep the same table which contains the same columns.










share|improve this question




















  • 1





    Please don't add external links to your question, unless it really contains critical information which can't be included directly in your question here. This does not appear to be the case.

    – Tim Biegeleisen
    Jan 1 at 4:57











  • Sorry about that. That was a Fairly new here. :P

    – 123testing123
    Jan 2 at 2:21
















-1















I have different counters named Available Bytes, Committed Bytes, Modified Page List Bytes with different values. I want to add the values from these counters and all the values are under CounterValue.



-----------------------------------------------------------------------------
Machine Name | Counter Name | Counter ID | CounterDateTime | Counter Value
------------------------------------------------------------------------------
Machine 1 |Available Bytes | 1 | Dec 31st | 123
Machine 1 |Committed Bytes | 2 | Dec 31st | 223
Machine 1 |Modified Bytes | 3 | Dec 31st | 345
Machine 2 |Available Bytes | 4 | Dec 31st | 467
Machine 2 |Committed Bytes | 5 | Dec 31st | 567
Machine 2 |Modified Bytes | 6 | Dec 31st | 678
Machine 3 |Available Bytes | 7 | Dec 31st | 467
Machine 3 |Committed Bytes | 8 | Dec 31st | 567
Machine 3 |Modified Bytes | 9 | Dec 31st | 678


How do I go about doing this?
counter



For instance, I want to do this:



1-[(Available Bytes)/(Available Byes + Committed Bytes + Modified Page List Bytes)] x 100 for each machine name



But I also want to keep the same table which contains the same columns.










share|improve this question




















  • 1





    Please don't add external links to your question, unless it really contains critical information which can't be included directly in your question here. This does not appear to be the case.

    – Tim Biegeleisen
    Jan 1 at 4:57











  • Sorry about that. That was a Fairly new here. :P

    – 123testing123
    Jan 2 at 2:21














-1












-1








-1








I have different counters named Available Bytes, Committed Bytes, Modified Page List Bytes with different values. I want to add the values from these counters and all the values are under CounterValue.



-----------------------------------------------------------------------------
Machine Name | Counter Name | Counter ID | CounterDateTime | Counter Value
------------------------------------------------------------------------------
Machine 1 |Available Bytes | 1 | Dec 31st | 123
Machine 1 |Committed Bytes | 2 | Dec 31st | 223
Machine 1 |Modified Bytes | 3 | Dec 31st | 345
Machine 2 |Available Bytes | 4 | Dec 31st | 467
Machine 2 |Committed Bytes | 5 | Dec 31st | 567
Machine 2 |Modified Bytes | 6 | Dec 31st | 678
Machine 3 |Available Bytes | 7 | Dec 31st | 467
Machine 3 |Committed Bytes | 8 | Dec 31st | 567
Machine 3 |Modified Bytes | 9 | Dec 31st | 678


How do I go about doing this?
counter



For instance, I want to do this:



1-[(Available Bytes)/(Available Byes + Committed Bytes + Modified Page List Bytes)] x 100 for each machine name



But I also want to keep the same table which contains the same columns.










share|improve this question
















I have different counters named Available Bytes, Committed Bytes, Modified Page List Bytes with different values. I want to add the values from these counters and all the values are under CounterValue.



-----------------------------------------------------------------------------
Machine Name | Counter Name | Counter ID | CounterDateTime | Counter Value
------------------------------------------------------------------------------
Machine 1 |Available Bytes | 1 | Dec 31st | 123
Machine 1 |Committed Bytes | 2 | Dec 31st | 223
Machine 1 |Modified Bytes | 3 | Dec 31st | 345
Machine 2 |Available Bytes | 4 | Dec 31st | 467
Machine 2 |Committed Bytes | 5 | Dec 31st | 567
Machine 2 |Modified Bytes | 6 | Dec 31st | 678
Machine 3 |Available Bytes | 7 | Dec 31st | 467
Machine 3 |Committed Bytes | 8 | Dec 31st | 567
Machine 3 |Modified Bytes | 9 | Dec 31st | 678


How do I go about doing this?
counter



For instance, I want to do this:



1-[(Available Bytes)/(Available Byes + Committed Bytes + Modified Page List Bytes)] x 100 for each machine name



But I also want to keep the same table which contains the same columns.







sql-server






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Jan 1 at 9:15









marc_s

577k12911151259




577k12911151259










asked Jan 1 at 4:53









123testing123123testing123

86




86








  • 1





    Please don't add external links to your question, unless it really contains critical information which can't be included directly in your question here. This does not appear to be the case.

    – Tim Biegeleisen
    Jan 1 at 4:57











  • Sorry about that. That was a Fairly new here. :P

    – 123testing123
    Jan 2 at 2:21














  • 1





    Please don't add external links to your question, unless it really contains critical information which can't be included directly in your question here. This does not appear to be the case.

    – Tim Biegeleisen
    Jan 1 at 4:57











  • Sorry about that. That was a Fairly new here. :P

    – 123testing123
    Jan 2 at 2:21








1




1





Please don't add external links to your question, unless it really contains critical information which can't be included directly in your question here. This does not appear to be the case.

– Tim Biegeleisen
Jan 1 at 4:57





Please don't add external links to your question, unless it really contains critical information which can't be included directly in your question here. This does not appear to be the case.

– Tim Biegeleisen
Jan 1 at 4:57













Sorry about that. That was a Fairly new here. :P

– 123testing123
Jan 2 at 2:21





Sorry about that. That was a Fairly new here. :P

– 123testing123
Jan 2 at 2:21












1 Answer
1






active

oldest

votes


















0














You can try conditional aggregation. Write a case, that only returns the value when the name is the one you want, 0 otherwise. Then sum it. Do this for all operands in your expression.



SELECT machine_name,
counterdatetime,
1
-
sum(CASE counter_name
WHEN 'Available Bytes' THEN
counter_value
ELSE
0
END)
/
(sum(CASE counter_name
WHEN 'Available Bytes' THEN
counter_value
ELSE
0
END)
+
sum(CASE counter_name
WHEN 'Comitted Bytes' THEN
counter_value
ELSE
0
END)
+
sum(CASE counter_name
WHEN 'Comitted Bytes' THEN
counter_value
ELSE
0
END)
+
sum(CASE counter_name
WHEN 'Modified Bytes' THEN
counter_value
ELSE
0
END)) counter_value
FROM elbat
GROUP BY machine_name,
counterdatetime;





share|improve this answer
























  • Thank you. That was I was looking for. But, I have multiple calculations to do. So one calculation is 1-[(Available Bytes)/(Available Byes + Committed Bytes + Modified Page List Bytes)] x 100 and the result is stored to a new column. However, I want to do another calculation unrelated to the first calculation so I created a sum(case) for this. I've tried doing this but it seems to be a stacked calculation, Is it possible to do multiple calculations returning result to different column?

    – 123testing123
    Jan 2 at 2:17











  • For instance, the cases you did above, I did: sum(case (bunch of calculation) AS column 1 and then what I want to do is do another sum(bunch of calculation unrelated to the first calculation) AS column 2.

    – 123testing123
    Jan 2 at 2:20











  • @123testing123: I don't really understand what you're asking but maybe sense a tendency... You can do whatever calculation that way, as long as there is a scalar expression in SQL Server for it and you use the sum(CASE ... END) as variables for it.

    – sticky bit
    Jan 2 at 2:21











  • @123testing123: And of course you can use other expressions (or even the same) in that form for a second, third, ... n-th column. Just append like , sum(...) <operator> sum(...) ... <column alias> to the list after FROM.

    – sticky bit
    Jan 2 at 2:29











Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53993086%2fsql-server-how-to-add-multiple-values-from-multiple-counters%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









0














You can try conditional aggregation. Write a case, that only returns the value when the name is the one you want, 0 otherwise. Then sum it. Do this for all operands in your expression.



SELECT machine_name,
counterdatetime,
1
-
sum(CASE counter_name
WHEN 'Available Bytes' THEN
counter_value
ELSE
0
END)
/
(sum(CASE counter_name
WHEN 'Available Bytes' THEN
counter_value
ELSE
0
END)
+
sum(CASE counter_name
WHEN 'Comitted Bytes' THEN
counter_value
ELSE
0
END)
+
sum(CASE counter_name
WHEN 'Comitted Bytes' THEN
counter_value
ELSE
0
END)
+
sum(CASE counter_name
WHEN 'Modified Bytes' THEN
counter_value
ELSE
0
END)) counter_value
FROM elbat
GROUP BY machine_name,
counterdatetime;





share|improve this answer
























  • Thank you. That was I was looking for. But, I have multiple calculations to do. So one calculation is 1-[(Available Bytes)/(Available Byes + Committed Bytes + Modified Page List Bytes)] x 100 and the result is stored to a new column. However, I want to do another calculation unrelated to the first calculation so I created a sum(case) for this. I've tried doing this but it seems to be a stacked calculation, Is it possible to do multiple calculations returning result to different column?

    – 123testing123
    Jan 2 at 2:17











  • For instance, the cases you did above, I did: sum(case (bunch of calculation) AS column 1 and then what I want to do is do another sum(bunch of calculation unrelated to the first calculation) AS column 2.

    – 123testing123
    Jan 2 at 2:20











  • @123testing123: I don't really understand what you're asking but maybe sense a tendency... You can do whatever calculation that way, as long as there is a scalar expression in SQL Server for it and you use the sum(CASE ... END) as variables for it.

    – sticky bit
    Jan 2 at 2:21











  • @123testing123: And of course you can use other expressions (or even the same) in that form for a second, third, ... n-th column. Just append like , sum(...) <operator> sum(...) ... <column alias> to the list after FROM.

    – sticky bit
    Jan 2 at 2:29
















0














You can try conditional aggregation. Write a case, that only returns the value when the name is the one you want, 0 otherwise. Then sum it. Do this for all operands in your expression.



SELECT machine_name,
counterdatetime,
1
-
sum(CASE counter_name
WHEN 'Available Bytes' THEN
counter_value
ELSE
0
END)
/
(sum(CASE counter_name
WHEN 'Available Bytes' THEN
counter_value
ELSE
0
END)
+
sum(CASE counter_name
WHEN 'Comitted Bytes' THEN
counter_value
ELSE
0
END)
+
sum(CASE counter_name
WHEN 'Comitted Bytes' THEN
counter_value
ELSE
0
END)
+
sum(CASE counter_name
WHEN 'Modified Bytes' THEN
counter_value
ELSE
0
END)) counter_value
FROM elbat
GROUP BY machine_name,
counterdatetime;





share|improve this answer
























  • Thank you. That was I was looking for. But, I have multiple calculations to do. So one calculation is 1-[(Available Bytes)/(Available Byes + Committed Bytes + Modified Page List Bytes)] x 100 and the result is stored to a new column. However, I want to do another calculation unrelated to the first calculation so I created a sum(case) for this. I've tried doing this but it seems to be a stacked calculation, Is it possible to do multiple calculations returning result to different column?

    – 123testing123
    Jan 2 at 2:17











  • For instance, the cases you did above, I did: sum(case (bunch of calculation) AS column 1 and then what I want to do is do another sum(bunch of calculation unrelated to the first calculation) AS column 2.

    – 123testing123
    Jan 2 at 2:20











  • @123testing123: I don't really understand what you're asking but maybe sense a tendency... You can do whatever calculation that way, as long as there is a scalar expression in SQL Server for it and you use the sum(CASE ... END) as variables for it.

    – sticky bit
    Jan 2 at 2:21











  • @123testing123: And of course you can use other expressions (or even the same) in that form for a second, third, ... n-th column. Just append like , sum(...) <operator> sum(...) ... <column alias> to the list after FROM.

    – sticky bit
    Jan 2 at 2:29














0












0








0







You can try conditional aggregation. Write a case, that only returns the value when the name is the one you want, 0 otherwise. Then sum it. Do this for all operands in your expression.



SELECT machine_name,
counterdatetime,
1
-
sum(CASE counter_name
WHEN 'Available Bytes' THEN
counter_value
ELSE
0
END)
/
(sum(CASE counter_name
WHEN 'Available Bytes' THEN
counter_value
ELSE
0
END)
+
sum(CASE counter_name
WHEN 'Comitted Bytes' THEN
counter_value
ELSE
0
END)
+
sum(CASE counter_name
WHEN 'Comitted Bytes' THEN
counter_value
ELSE
0
END)
+
sum(CASE counter_name
WHEN 'Modified Bytes' THEN
counter_value
ELSE
0
END)) counter_value
FROM elbat
GROUP BY machine_name,
counterdatetime;





share|improve this answer













You can try conditional aggregation. Write a case, that only returns the value when the name is the one you want, 0 otherwise. Then sum it. Do this for all operands in your expression.



SELECT machine_name,
counterdatetime,
1
-
sum(CASE counter_name
WHEN 'Available Bytes' THEN
counter_value
ELSE
0
END)
/
(sum(CASE counter_name
WHEN 'Available Bytes' THEN
counter_value
ELSE
0
END)
+
sum(CASE counter_name
WHEN 'Comitted Bytes' THEN
counter_value
ELSE
0
END)
+
sum(CASE counter_name
WHEN 'Comitted Bytes' THEN
counter_value
ELSE
0
END)
+
sum(CASE counter_name
WHEN 'Modified Bytes' THEN
counter_value
ELSE
0
END)) counter_value
FROM elbat
GROUP BY machine_name,
counterdatetime;






share|improve this answer












share|improve this answer



share|improve this answer










answered Jan 1 at 5:12









sticky bitsticky bit

15.1k101632




15.1k101632













  • Thank you. That was I was looking for. But, I have multiple calculations to do. So one calculation is 1-[(Available Bytes)/(Available Byes + Committed Bytes + Modified Page List Bytes)] x 100 and the result is stored to a new column. However, I want to do another calculation unrelated to the first calculation so I created a sum(case) for this. I've tried doing this but it seems to be a stacked calculation, Is it possible to do multiple calculations returning result to different column?

    – 123testing123
    Jan 2 at 2:17











  • For instance, the cases you did above, I did: sum(case (bunch of calculation) AS column 1 and then what I want to do is do another sum(bunch of calculation unrelated to the first calculation) AS column 2.

    – 123testing123
    Jan 2 at 2:20











  • @123testing123: I don't really understand what you're asking but maybe sense a tendency... You can do whatever calculation that way, as long as there is a scalar expression in SQL Server for it and you use the sum(CASE ... END) as variables for it.

    – sticky bit
    Jan 2 at 2:21











  • @123testing123: And of course you can use other expressions (or even the same) in that form for a second, third, ... n-th column. Just append like , sum(...) <operator> sum(...) ... <column alias> to the list after FROM.

    – sticky bit
    Jan 2 at 2:29



















  • Thank you. That was I was looking for. But, I have multiple calculations to do. So one calculation is 1-[(Available Bytes)/(Available Byes + Committed Bytes + Modified Page List Bytes)] x 100 and the result is stored to a new column. However, I want to do another calculation unrelated to the first calculation so I created a sum(case) for this. I've tried doing this but it seems to be a stacked calculation, Is it possible to do multiple calculations returning result to different column?

    – 123testing123
    Jan 2 at 2:17











  • For instance, the cases you did above, I did: sum(case (bunch of calculation) AS column 1 and then what I want to do is do another sum(bunch of calculation unrelated to the first calculation) AS column 2.

    – 123testing123
    Jan 2 at 2:20











  • @123testing123: I don't really understand what you're asking but maybe sense a tendency... You can do whatever calculation that way, as long as there is a scalar expression in SQL Server for it and you use the sum(CASE ... END) as variables for it.

    – sticky bit
    Jan 2 at 2:21











  • @123testing123: And of course you can use other expressions (or even the same) in that form for a second, third, ... n-th column. Just append like , sum(...) <operator> sum(...) ... <column alias> to the list after FROM.

    – sticky bit
    Jan 2 at 2:29

















Thank you. That was I was looking for. But, I have multiple calculations to do. So one calculation is 1-[(Available Bytes)/(Available Byes + Committed Bytes + Modified Page List Bytes)] x 100 and the result is stored to a new column. However, I want to do another calculation unrelated to the first calculation so I created a sum(case) for this. I've tried doing this but it seems to be a stacked calculation, Is it possible to do multiple calculations returning result to different column?

– 123testing123
Jan 2 at 2:17





Thank you. That was I was looking for. But, I have multiple calculations to do. So one calculation is 1-[(Available Bytes)/(Available Byes + Committed Bytes + Modified Page List Bytes)] x 100 and the result is stored to a new column. However, I want to do another calculation unrelated to the first calculation so I created a sum(case) for this. I've tried doing this but it seems to be a stacked calculation, Is it possible to do multiple calculations returning result to different column?

– 123testing123
Jan 2 at 2:17













For instance, the cases you did above, I did: sum(case (bunch of calculation) AS column 1 and then what I want to do is do another sum(bunch of calculation unrelated to the first calculation) AS column 2.

– 123testing123
Jan 2 at 2:20





For instance, the cases you did above, I did: sum(case (bunch of calculation) AS column 1 and then what I want to do is do another sum(bunch of calculation unrelated to the first calculation) AS column 2.

– 123testing123
Jan 2 at 2:20













@123testing123: I don't really understand what you're asking but maybe sense a tendency... You can do whatever calculation that way, as long as there is a scalar expression in SQL Server for it and you use the sum(CASE ... END) as variables for it.

– sticky bit
Jan 2 at 2:21





@123testing123: I don't really understand what you're asking but maybe sense a tendency... You can do whatever calculation that way, as long as there is a scalar expression in SQL Server for it and you use the sum(CASE ... END) as variables for it.

– sticky bit
Jan 2 at 2:21













@123testing123: And of course you can use other expressions (or even the same) in that form for a second, third, ... n-th column. Just append like , sum(...) <operator> sum(...) ... <column alias> to the list after FROM.

– sticky bit
Jan 2 at 2:29





@123testing123: And of course you can use other expressions (or even the same) in that form for a second, third, ... n-th column. Just append like , sum(...) <operator> sum(...) ... <column alias> to the list after FROM.

– sticky bit
Jan 2 at 2:29




















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53993086%2fsql-server-how-to-add-multiple-values-from-multiple-counters%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







uGh ukUYn,XJck 2E1
S3PzBL1I4Oy

Popular posts from this blog

Monofisismo

Angular Downloading a file using contenturl with Basic Authentication

Olmecas