SQL Server: How to add multiple values from multiple counters

Multi tool use
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?
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.

add a comment |
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?
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.

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
add a comment |
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?
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.

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?
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.


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
add a comment |
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
add a comment |
1 Answer
1
active
oldest
votes
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;
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 thesum(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 afterFROM
.
– sticky bit
Jan 2 at 2:29
add a comment |
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
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
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;
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 thesum(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 afterFROM
.
– sticky bit
Jan 2 at 2:29
add a comment |
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;
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 thesum(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 afterFROM
.
– sticky bit
Jan 2 at 2:29
add a comment |
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;
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;
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 thesum(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 afterFROM
.
– sticky bit
Jan 2 at 2:29
add a comment |
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 thesum(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 afterFROM
.
– 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
add a comment |
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.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
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
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
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
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