How to add grand total to this query?
1
With this query:
With EX1 AS
(
SELECT
idcompany,
idemploy,
import
FROM data
WHERE
idcompany in ('000405', '000102')
)
SELECT *
FROM EX1
UNION ALL
SELECT idcompany, 'Total', sum(import) from EX1
GROUP BY idcompany
ORDER BY
idcompany,
idemploy
I get:
idcompany idemploy import
000102 0000001 100
000102 0000002 200
000102 Total 300
000405 0000001 50
000405 0000002 70
000405 Total 120
And I'd like to get:
idcompany idemploy import
000102 0000001 100
000102 0000002 200
000102 Total 300
000405 0000001 50
000405 0000002 70
000405 Total 120
Grand Total 420
What code do I have to add to the query, and where should I add it? Thanks.
sql postgresql
edited Nov 15 '18 at 15:23
Kaushik Nayak
20.4k41332
asked Nov 15 '18 at 11:32
Lucia DeetzLucia Deetz
62
add a comment |
1
With this query:
With EX1 AS
(
SELECT
idcompany,
idemploy,
import
FROM data
WHERE
idcompany in ('000405', '000102')
)
SELECT *
FROM EX1
UNION ALL
SELECT idcompany, 'Total', sum(import) from EX1
GROUP BY idcompany
ORDER BY
idcompany,
idemploy
I get:
idcompany idemploy import
000102 0000001 100
000102 0000002 200
000102 Total 300
000405 0000001 50
000405 0000002 70
000405 Total 120
And I'd like to get:
idcompany idemploy import
000102 0000001 100
000102 0000002 200
000102 Total 300
000405 0000001 50
000405 0000002 70
000405 Total 120
Grand Total 420
What code do I have to add to the query, and where should I add it? Thanks.
sql postgresql
edited Nov 15 '18 at 15:23
Kaushik Nayak
20.4k41332
asked Nov 15 '18 at 11:32
Lucia DeetzLucia Deetz
62
if your question is answered, please mark it answered.
– Selaron
Jan 15 at 10:19
add a comment |
1
1
1
With this query:
With EX1 AS
(
SELECT
idcompany,
idemploy,
import
FROM data
WHERE
idcompany in ('000405', '000102')
)
SELECT *
FROM EX1
UNION ALL
SELECT idcompany, 'Total', sum(import) from EX1
GROUP BY idcompany
ORDER BY
idcompany,
idemploy
I get:
idcompany idemploy import
000102 0000001 100
000102 0000002 200
000102 Total 300
000405 0000001 50
000405 0000002 70
000405 Total 120
And I'd like to get:
idcompany idemploy import
000102 0000001 100
000102 0000002 200
000102 Total 300
000405 0000001 50
000405 0000002 70
000405 Total 120
Grand Total 420
What code do I have to add to the query, and where should I add it? Thanks.
sql postgresql
edited Nov 15 '18 at 15:23
Kaushik Nayak
20.4k41332
asked Nov 15 '18 at 11:32
Lucia DeetzLucia Deetz
62
With this query:
With EX1 AS
(
SELECT
idcompany,
idemploy,
import
FROM data
WHERE
idcompany in ('000405', '000102')
)
SELECT *
FROM EX1
UNION ALL
SELECT idcompany, 'Total', sum(import) from EX1
GROUP BY idcompany
ORDER BY
idcompany,
idemploy
I get:
idcompany idemploy import
000102 0000001 100
000102 0000002 200
000102 Total 300
000405 0000001 50
000405 0000002 70
000405 Total 120
And I'd like to get:
idcompany idemploy import
000102 0000001 100
000102 0000002 200
000102 Total 300
000405 0000001 50
000405 0000002 70
000405 Total 120
Grand Total 420
What code do I have to add to the query, and where should I add it? Thanks.
sql postgresql
sql postgresql
edited Nov 15 '18 at 15:23
Kaushik Nayak
20.4k41332
asked Nov 15 '18 at 11:32
Lucia DeetzLucia Deetz
62
edited Nov 15 '18 at 15:23
Kaushik Nayak
20.4k41332
asked Nov 15 '18 at 11:32
Lucia DeetzLucia Deetz
62
edited Nov 15 '18 at 15:23
Kaushik Nayak
20.4k41332
edited Nov 15 '18 at 15:23
Kaushik Nayak
20.4k41332
edited Nov 15 '18 at 15:23
Kaushik Nayak
20.4k41332
20.4k41332
asked Nov 15 '18 at 11:32
Lucia DeetzLucia Deetz
62
asked Nov 15 '18 at 11:32
Lucia DeetzLucia Deetz
62
asked Nov 15 '18 at 11:32
Lucia DeetzLucia Deetz
62
62
if your question is answered, please mark it answered.
– Selaron
Jan 15 at 10:19
add a comment |
if your question is answered, please mark it answered.
– Selaron
Jan 15 at 10:19
if your question is answered, please mark it answered.
– Selaron
Jan 15 at 10:19
if your question is answered, please mark it answered.
– Selaron
Jan 15 at 10:19
add a comment |
2 Answers
2
active
oldest
votes
2
You can append a
UNION ALL
SELECT 'Grand', 'Total', sum(import) from EX1
to your query.
answered Nov 15 '18 at 11:49
SelaronSelaron
2,73111724
Where do I need to append this line of code in the query?
– Lucia Deetz
Nov 15 '18 at 12:46
1
@LuciaDeetz: the same way you did with theUNION ALLin your question
– a_horse_with_no_name
Nov 15 '18 at 15:25
Append "b" to a String "a" would result in "ab".
– Selaron
Nov 15 '18 at 15:28
add a comment |
0
You may use the ROLLUP sub-clause of the GROUP BY clause (Postgres 9.5+) along with the Grouping function which is ideally suited for such operations and is efficient than those UNION ALLs
Docs
SELECT CASE
WHEN GROUPING(idcompany) = 1
AND GROUPING(idemploy) = 1
THEN 'Grand'
ELSE idcompany
END AS idcompany
,CASE GROUPING(idemploy)
WHEN 1
THEN 'Total'
ELSE idemploy
END AS idemploy
,SUM(import)
FROM data
GROUP BY ROLLUP(idcompany, idemploy);
Demo
answered Nov 15 '18 at 15:22
Kaushik NayakKaushik Nayak
20.4k41332
Thank you, but unfortunately we're using Postgresql 9.4.
– Lucia Deetz
Nov 15 '18 at 15: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
);
);
draft saved
draft discarded
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%2f53318532%2fhow-to-add-grand-total-to-this-query%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
2
You can append a
UNION ALL
SELECT 'Grand', 'Total', sum(import) from EX1
to your query.
answered Nov 15 '18 at 11:49
SelaronSelaron
2,73111724
Where do I need to append this line of code in the query?
– Lucia Deetz
Nov 15 '18 at 12:46
1
@LuciaDeetz: the same way you did with theUNION ALLin your question
– a_horse_with_no_name
Nov 15 '18 at 15:25
Append "b" to a String "a" would result in "ab".
– Selaron
Nov 15 '18 at 15:28
add a comment |
2
You can append a
UNION ALL
SELECT 'Grand', 'Total', sum(import) from EX1
to your query.
answered Nov 15 '18 at 11:49
SelaronSelaron
2,73111724
Where do I need to append this line of code in the query?
– Lucia Deetz
Nov 15 '18 at 12:46
1
@LuciaDeetz: the same way you did with theUNION ALLin your question
– a_horse_with_no_name
Nov 15 '18 at 15:25
Append "b" to a String "a" would result in "ab".
– Selaron
Nov 15 '18 at 15:28
add a comment |
2
2
2
You can append a
UNION ALL
SELECT 'Grand', 'Total', sum(import) from EX1
to your query.
answered Nov 15 '18 at 11:49
SelaronSelaron
2,73111724
You can append a
UNION ALL
SELECT 'Grand', 'Total', sum(import) from EX1
to your query.
answered Nov 15 '18 at 11:49
SelaronSelaron
2,73111724
answered Nov 15 '18 at 11:49
SelaronSelaron
2,73111724
answered Nov 15 '18 at 11:49
SelaronSelaron
2,73111724
answered Nov 15 '18 at 11:49
SelaronSelaron
2,73111724
2,73111724
Where do I need to append this line of code in the query?
– Lucia Deetz
Nov 15 '18 at 12:46
1
@LuciaDeetz: the same way you did with theUNION ALLin your question
– a_horse_with_no_name
Nov 15 '18 at 15:25
Append "b" to a String "a" would result in "ab".
– Selaron
Nov 15 '18 at 15:28
add a comment |
Where do I need to append this line of code in the query?
– Lucia Deetz
Nov 15 '18 at 12:46
1
@LuciaDeetz: the same way you did with theUNION ALLin your question
– a_horse_with_no_name
Nov 15 '18 at 15:25
Append "b" to a String "a" would result in "ab".
– Selaron
Nov 15 '18 at 15:28
Where do I need to append this line of code in the query?
– Lucia Deetz
Nov 15 '18 at 12:46
Where do I need to append this line of code in the query?
– Lucia Deetz
Nov 15 '18 at 12:46
1
1
@LuciaDeetz: the same way you did with the
UNION ALL in your question– a_horse_with_no_name
Nov 15 '18 at 15:25
@LuciaDeetz: the same way you did with the
UNION ALL in your question– a_horse_with_no_name
Nov 15 '18 at 15:25
Append "b" to a String "a" would result in "ab".
– Selaron
Nov 15 '18 at 15:28
Append "b" to a String "a" would result in "ab".
– Selaron
Nov 15 '18 at 15:28
add a comment |
0
You may use the ROLLUP sub-clause of the GROUP BY clause (Postgres 9.5+) along with the Grouping function which is ideally suited for such operations and is efficient than those UNION ALLs
Docs
SELECT CASE
WHEN GROUPING(idcompany) = 1
AND GROUPING(idemploy) = 1
THEN 'Grand'
ELSE idcompany
END AS idcompany
,CASE GROUPING(idemploy)
WHEN 1
THEN 'Total'
ELSE idemploy
END AS idemploy
,SUM(import)
FROM data
GROUP BY ROLLUP(idcompany, idemploy);
Demo
answered Nov 15 '18 at 15:22
Kaushik NayakKaushik Nayak
20.4k41332
Thank you, but unfortunately we're using Postgresql 9.4.
– Lucia Deetz
Nov 15 '18 at 15:29
add a comment |
0
You may use the ROLLUP sub-clause of the GROUP BY clause (Postgres 9.5+) along with the Grouping function which is ideally suited for such operations and is efficient than those UNION ALLs
Docs
SELECT CASE
WHEN GROUPING(idcompany) = 1
AND GROUPING(idemploy) = 1
THEN 'Grand'
ELSE idcompany
END AS idcompany
,CASE GROUPING(idemploy)
WHEN 1
THEN 'Total'
ELSE idemploy
END AS idemploy
,SUM(import)
FROM data
GROUP BY ROLLUP(idcompany, idemploy);
Demo
answered Nov 15 '18 at 15:22
Kaushik NayakKaushik Nayak
20.4k41332
Thank you, but unfortunately we're using Postgresql 9.4.
– Lucia Deetz
Nov 15 '18 at 15:29
add a comment |
0
0
0
You may use the ROLLUP sub-clause of the GROUP BY clause (Postgres 9.5+) along with the Grouping function which is ideally suited for such operations and is efficient than those UNION ALLs
Docs
SELECT CASE
WHEN GROUPING(idcompany) = 1
AND GROUPING(idemploy) = 1
THEN 'Grand'
ELSE idcompany
END AS idcompany
,CASE GROUPING(idemploy)
WHEN 1
THEN 'Total'
ELSE idemploy
END AS idemploy
,SUM(import)
FROM data
GROUP BY ROLLUP(idcompany, idemploy);
Demo
answered Nov 15 '18 at 15:22
Kaushik NayakKaushik Nayak
20.4k41332
You may use the ROLLUP sub-clause of the GROUP BY clause (Postgres 9.5+) along with the Grouping function which is ideally suited for such operations and is efficient than those UNION ALLs
Docs
SELECT CASE
WHEN GROUPING(idcompany) = 1
AND GROUPING(idemploy) = 1
THEN 'Grand'
ELSE idcompany
END AS idcompany
,CASE GROUPING(idemploy)
WHEN 1
THEN 'Total'
ELSE idemploy
END AS idemploy
,SUM(import)
FROM data
GROUP BY ROLLUP(idcompany, idemploy);
Demo
answered Nov 15 '18 at 15:22
Kaushik NayakKaushik Nayak
20.4k41332
answered Nov 15 '18 at 15:22
Kaushik NayakKaushik Nayak
20.4k41332
answered Nov 15 '18 at 15:22
Kaushik NayakKaushik Nayak
20.4k41332
answered Nov 15 '18 at 15:22
Kaushik NayakKaushik Nayak
20.4k41332
20.4k41332
Thank you, but unfortunately we're using Postgresql 9.4.
– Lucia Deetz
Nov 15 '18 at 15:29
add a comment |
Thank you, but unfortunately we're using Postgresql 9.4.
– Lucia Deetz
Nov 15 '18 at 15:29
Thank you, but unfortunately we're using Postgresql 9.4.
– Lucia Deetz
Nov 15 '18 at 15:29
Thank you, but unfortunately we're using Postgresql 9.4.
– Lucia Deetz
Nov 15 '18 at 15:29
add a comment |
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
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%2f53318532%2fhow-to-add-grand-total-to-this-query%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
if your question is answered, please mark it answered.
– Selaron
Jan 15 at 10:19