How to divide sum in number from count in having?
I have the following SQL query:
SELECT SUM(m1) as m1,
SUM(m2) as m2,
SUM(m3) as m3,
SUM(m4) as m4 FROM `test`
GROUP BY type HAVING count(*) > 300;
I need to divide each SUM on value count(*) in having condition. Like this:
SELECT SUM(m1) / count(*) as m1,
SUM(m2) / count(*) as m2,
SUM(m3) / count(*) as m3,
SUM(m4) / count(*) as m4 FROM `test`
GROUP BY type HAVING count(*) > 300;
How to improve this query, avoiding / count(*) in each operation SUM()?
sql
asked Nov 25 '18 at 22:03
OPVOPV
1,71921542
add a comment |
I have the following SQL query:
SELECT SUM(m1) as m1,
SUM(m2) as m2,
SUM(m3) as m3,
SUM(m4) as m4 FROM `test`
GROUP BY type HAVING count(*) > 300;
I need to divide each SUM on value count(*) in having condition. Like this:
SELECT SUM(m1) / count(*) as m1,
SUM(m2) / count(*) as m2,
SUM(m3) / count(*) as m3,
SUM(m4) / count(*) as m4 FROM `test`
GROUP BY type HAVING count(*) > 300;
How to improve this query, avoiding / count(*) in each operation SUM()?
sql
asked Nov 25 '18 at 22:03
OPVOPV
1,71921542
I tried to use also subquery
– OPV
Nov 25 '18 at 22:05
1
that looks remarkably like you're calculating "arithmetic means"; there's a built in operator, calledavg, just for that. any reason you're not using it?
– Sam Mason
Nov 25 '18 at 22:09
Sure, you are right I need just replaceSUM(m)onAVG(m1)it works
– OPV
Nov 25 '18 at 22:10
Post this as answer I will apply
– OPV
Nov 25 '18 at 22:10
Replace everySUM(m?) / count(*)withAVG(m?)
– forpas
Nov 25 '18 at 22:11
add a comment |
I have the following SQL query:
SELECT SUM(m1) as m1,
SUM(m2) as m2,
SUM(m3) as m3,
SUM(m4) as m4 FROM `test`
GROUP BY type HAVING count(*) > 300;
I need to divide each SUM on value count(*) in having condition. Like this:
SELECT SUM(m1) / count(*) as m1,
SUM(m2) / count(*) as m2,
SUM(m3) / count(*) as m3,
SUM(m4) / count(*) as m4 FROM `test`
GROUP BY type HAVING count(*) > 300;
How to improve this query, avoiding / count(*) in each operation SUM()?
sql
asked Nov 25 '18 at 22:03
OPVOPV
1,71921542
I have the following SQL query:
SELECT SUM(m1) as m1,
SUM(m2) as m2,
SUM(m3) as m3,
SUM(m4) as m4 FROM `test`
GROUP BY type HAVING count(*) > 300;
I need to divide each SUM on value count(*) in having condition. Like this:
SELECT SUM(m1) / count(*) as m1,
SUM(m2) / count(*) as m2,
SUM(m3) / count(*) as m3,
SUM(m4) / count(*) as m4 FROM `test`
GROUP BY type HAVING count(*) > 300;
How to improve this query, avoiding / count(*) in each operation SUM()?
sql
sql
asked Nov 25 '18 at 22:03
OPVOPV
1,71921542
asked Nov 25 '18 at 22:03
OPVOPV
1,71921542
asked Nov 25 '18 at 22:03
OPVOPV
1,71921542
asked Nov 25 '18 at 22:03
OPVOPV
1,71921542
asked Nov 25 '18 at 22:03
OPVOPV
1,71921542
1,71921542
I tried to use also subquery
– OPV
Nov 25 '18 at 22:05
1
that looks remarkably like you're calculating "arithmetic means"; there's a built in operator, calledavg, just for that. any reason you're not using it?
– Sam Mason
Nov 25 '18 at 22:09
Sure, you are right I need just replaceSUM(m)onAVG(m1)it works
– OPV
Nov 25 '18 at 22:10
Post this as answer I will apply
– OPV
Nov 25 '18 at 22:10
Replace everySUM(m?) / count(*)withAVG(m?)
– forpas
Nov 25 '18 at 22:11
add a comment |
I tried to use also subquery
– OPV
Nov 25 '18 at 22:05
1
that looks remarkably like you're calculating "arithmetic means"; there's a built in operator, calledavg, just for that. any reason you're not using it?
– Sam Mason
Nov 25 '18 at 22:09
Sure, you are right I need just replaceSUM(m)onAVG(m1)it works
– OPV
Nov 25 '18 at 22:10
Post this as answer I will apply
– OPV
Nov 25 '18 at 22:10
Replace everySUM(m?) / count(*)withAVG(m?)
– forpas
Nov 25 '18 at 22:11
I tried to use also subquery
– OPV
Nov 25 '18 at 22:05
I tried to use also subquery
– OPV
Nov 25 '18 at 22:05
1
1
that looks remarkably like you're calculating "arithmetic means"; there's a built in operator, called
avg, just for that. any reason you're not using it?– Sam Mason
Nov 25 '18 at 22:09
that looks remarkably like you're calculating "arithmetic means"; there's a built in operator, called
avg, just for that. any reason you're not using it?– Sam Mason
Nov 25 '18 at 22:09
Sure, you are right I need just replace
SUM(m) on AVG(m1) it works– OPV
Nov 25 '18 at 22:10
Sure, you are right I need just replace
SUM(m) on AVG(m1) it works– OPV
Nov 25 '18 at 22:10
Post this as answer I will apply
– OPV
Nov 25 '18 at 22:10
Post this as answer I will apply
– OPV
Nov 25 '18 at 22:10
Replace every
SUM(m?) / count(*) with AVG(m?)– forpas
Nov 25 '18 at 22:11
Replace every
SUM(m?) / count(*) with AVG(m?)– forpas
Nov 25 '18 at 22:11
add a comment |
3 Answers
3
active
oldest
votes
The canonical solution is avg() -- and that is probably what you really intend:
SELECT AVG(m1) as m1, AVG(m2) as m2, AVG(m3) as m3, AVG(m4) as m4
FROM `test`
GROUP BY type
HAVING count(*) > 300;
However, this is really equivalent to:
SELECT SUM(m1) / count(m1) as m1,
SUM(m2) / count(m2) as m2,
SUM(m3) / count(m3) as m3,
SUM(m4) / count(m4) as m4
FROM `test`
GROUP BY type
HAVING count(*) > 300;
COUNT(*) is not the same as COUNT(m1) because of NULL values. The formal equivalent would be:
SELECT AVG(COALESCE(m1, 0)) as m1,
AVG(COALESCE(m2, 0)) as m2,
AVG(COALESCE(m3, 0)) as m3,
AVG(COALESCE(m4, 0)) as m4
FROM `test`
GROUP BY type
HAVING count(*) > 300;
As I say, though, you probably intend the simple AVG(), but this is the correct equivalent.
answered Nov 25 '18 at 22:24
Gordon LinoffGordon Linoff
788k35313418
add a comment |
looks like you just want to calculate averages, e.g:
SELECT
AVG(m1) as m1, AVG(m2) as m2 -- …
FROM test
GROUP BY type
HAVING count(*) > 300;
would do what you want, there are lots more aggregate functions here:
https://www.postgresql.org/docs/11/functions-aggregate.html
answered Nov 25 '18 at 22:13
Sam MasonSam Mason
3,34811331
add a comment |
Cant you just use the AVG() function?
if not, perform a count first and use the result in the next query, but AVG() is better, or are you trying to do something else entirely?
answered Nov 25 '18 at 22:13
redgenieukredgenieuk
1006
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%2f53472467%2fhow-to-divide-sum-in-number-from-count-in-having%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
The canonical solution is avg() -- and that is probably what you really intend:
SELECT AVG(m1) as m1, AVG(m2) as m2, AVG(m3) as m3, AVG(m4) as m4
FROM `test`
GROUP BY type
HAVING count(*) > 300;
However, this is really equivalent to:
SELECT SUM(m1) / count(m1) as m1,
SUM(m2) / count(m2) as m2,
SUM(m3) / count(m3) as m3,
SUM(m4) / count(m4) as m4
FROM `test`
GROUP BY type
HAVING count(*) > 300;
COUNT(*) is not the same as COUNT(m1) because of NULL values. The formal equivalent would be:
SELECT AVG(COALESCE(m1, 0)) as m1,
AVG(COALESCE(m2, 0)) as m2,
AVG(COALESCE(m3, 0)) as m3,
AVG(COALESCE(m4, 0)) as m4
FROM `test`
GROUP BY type
HAVING count(*) > 300;
As I say, though, you probably intend the simple AVG(), but this is the correct equivalent.
answered Nov 25 '18 at 22:24
Gordon LinoffGordon Linoff
788k35313418
add a comment |
The canonical solution is avg() -- and that is probably what you really intend:
SELECT AVG(m1) as m1, AVG(m2) as m2, AVG(m3) as m3, AVG(m4) as m4
FROM `test`
GROUP BY type
HAVING count(*) > 300;
However, this is really equivalent to:
SELECT SUM(m1) / count(m1) as m1,
SUM(m2) / count(m2) as m2,
SUM(m3) / count(m3) as m3,
SUM(m4) / count(m4) as m4
FROM `test`
GROUP BY type
HAVING count(*) > 300;
COUNT(*) is not the same as COUNT(m1) because of NULL values. The formal equivalent would be:
SELECT AVG(COALESCE(m1, 0)) as m1,
AVG(COALESCE(m2, 0)) as m2,
AVG(COALESCE(m3, 0)) as m3,
AVG(COALESCE(m4, 0)) as m4
FROM `test`
GROUP BY type
HAVING count(*) > 300;
As I say, though, you probably intend the simple AVG(), but this is the correct equivalent.
answered Nov 25 '18 at 22:24
Gordon LinoffGordon Linoff
788k35313418
add a comment |
The canonical solution is avg() -- and that is probably what you really intend:
SELECT AVG(m1) as m1, AVG(m2) as m2, AVG(m3) as m3, AVG(m4) as m4
FROM `test`
GROUP BY type
HAVING count(*) > 300;
However, this is really equivalent to:
SELECT SUM(m1) / count(m1) as m1,
SUM(m2) / count(m2) as m2,
SUM(m3) / count(m3) as m3,
SUM(m4) / count(m4) as m4
FROM `test`
GROUP BY type
HAVING count(*) > 300;
COUNT(*) is not the same as COUNT(m1) because of NULL values. The formal equivalent would be:
SELECT AVG(COALESCE(m1, 0)) as m1,
AVG(COALESCE(m2, 0)) as m2,
AVG(COALESCE(m3, 0)) as m3,
AVG(COALESCE(m4, 0)) as m4
FROM `test`
GROUP BY type
HAVING count(*) > 300;
As I say, though, you probably intend the simple AVG(), but this is the correct equivalent.
answered Nov 25 '18 at 22:24
Gordon LinoffGordon Linoff
788k35313418
The canonical solution is avg() -- and that is probably what you really intend:
SELECT AVG(m1) as m1, AVG(m2) as m2, AVG(m3) as m3, AVG(m4) as m4
FROM `test`
GROUP BY type
HAVING count(*) > 300;
However, this is really equivalent to:
SELECT SUM(m1) / count(m1) as m1,
SUM(m2) / count(m2) as m2,
SUM(m3) / count(m3) as m3,
SUM(m4) / count(m4) as m4
FROM `test`
GROUP BY type
HAVING count(*) > 300;
COUNT(*) is not the same as COUNT(m1) because of NULL values. The formal equivalent would be:
SELECT AVG(COALESCE(m1, 0)) as m1,
AVG(COALESCE(m2, 0)) as m2,
AVG(COALESCE(m3, 0)) as m3,
AVG(COALESCE(m4, 0)) as m4
FROM `test`
GROUP BY type
HAVING count(*) > 300;
As I say, though, you probably intend the simple AVG(), but this is the correct equivalent.
answered Nov 25 '18 at 22:24
Gordon LinoffGordon Linoff
788k35313418
answered Nov 25 '18 at 22:24
Gordon LinoffGordon Linoff
788k35313418
answered Nov 25 '18 at 22:24
Gordon LinoffGordon Linoff
788k35313418
answered Nov 25 '18 at 22:24
Gordon LinoffGordon Linoff
788k35313418
788k35313418
add a comment |
add a comment |
looks like you just want to calculate averages, e.g:
SELECT
AVG(m1) as m1, AVG(m2) as m2 -- …
FROM test
GROUP BY type
HAVING count(*) > 300;
would do what you want, there are lots more aggregate functions here:
https://www.postgresql.org/docs/11/functions-aggregate.html
answered Nov 25 '18 at 22:13
Sam MasonSam Mason
3,34811331
add a comment |
looks like you just want to calculate averages, e.g:
SELECT
AVG(m1) as m1, AVG(m2) as m2 -- …
FROM test
GROUP BY type
HAVING count(*) > 300;
would do what you want, there are lots more aggregate functions here:
https://www.postgresql.org/docs/11/functions-aggregate.html
answered Nov 25 '18 at 22:13
Sam MasonSam Mason
3,34811331
add a comment |
looks like you just want to calculate averages, e.g:
SELECT
AVG(m1) as m1, AVG(m2) as m2 -- …
FROM test
GROUP BY type
HAVING count(*) > 300;
would do what you want, there are lots more aggregate functions here:
https://www.postgresql.org/docs/11/functions-aggregate.html
answered Nov 25 '18 at 22:13
Sam MasonSam Mason
3,34811331
looks like you just want to calculate averages, e.g:
SELECT
AVG(m1) as m1, AVG(m2) as m2 -- …
FROM test
GROUP BY type
HAVING count(*) > 300;
would do what you want, there are lots more aggregate functions here:
https://www.postgresql.org/docs/11/functions-aggregate.html
answered Nov 25 '18 at 22:13
Sam MasonSam Mason
3,34811331
answered Nov 25 '18 at 22:13
Sam MasonSam Mason
3,34811331
answered Nov 25 '18 at 22:13
Sam MasonSam Mason
3,34811331
answered Nov 25 '18 at 22:13
Sam MasonSam Mason
3,34811331
3,34811331
add a comment |
add a comment |
Cant you just use the AVG() function?
if not, perform a count first and use the result in the next query, but AVG() is better, or are you trying to do something else entirely?
answered Nov 25 '18 at 22:13
redgenieukredgenieuk
1006
add a comment |
Cant you just use the AVG() function?
if not, perform a count first and use the result in the next query, but AVG() is better, or are you trying to do something else entirely?
answered Nov 25 '18 at 22:13
redgenieukredgenieuk
1006
add a comment |
Cant you just use the AVG() function?
if not, perform a count first and use the result in the next query, but AVG() is better, or are you trying to do something else entirely?
answered Nov 25 '18 at 22:13
redgenieukredgenieuk
1006
Cant you just use the AVG() function?
if not, perform a count first and use the result in the next query, but AVG() is better, or are you trying to do something else entirely?
answered Nov 25 '18 at 22:13
redgenieukredgenieuk
1006
answered Nov 25 '18 at 22:13
redgenieukredgenieuk
1006
answered Nov 25 '18 at 22:13
redgenieukredgenieuk
1006
answered Nov 25 '18 at 22:13
redgenieukredgenieuk
1006
1006
add a comment |
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%2f53472467%2fhow-to-divide-sum-in-number-from-count-in-having%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
I tried to use also subquery
– OPV
Nov 25 '18 at 22:05
1
that looks remarkably like you're calculating "arithmetic means"; there's a built in operator, called
avg, just for that. any reason you're not using it?– Sam Mason
Nov 25 '18 at 22:09
Sure, you are right I need just replace
SUM(m)onAVG(m1)it works– OPV
Nov 25 '18 at 22:10
Post this as answer I will apply
– OPV
Nov 25 '18 at 22:10
Replace every
SUM(m?) / count(*)withAVG(m?)– forpas
Nov 25 '18 at 22:11