mysql sum and show all registries

-1

im trying to sum and show all registries in mysql.
i have this query:

select `deliverables`.*, 

       `users`.`first_name`, 

       `users`.`last_name` 

from `deliverables` 

inner join `users` on `users`.`id` = `deliverables`.`user_id` 

where `deliverables`.`specialty_id` = '11' and 

      `deliverables`.`role_id` <> '1'

and the output is:

file_code  file_name       dedicated_hours



0001asder  test-file.docx  4

0001as234w asdf.doc        2

jgfjh2546  test.docx       4

0001asder  test-file.docx  1

0001asder  test-file.docx  0

0001asder  test-file.docx  0

i need sum the dedicated_hours where file_code is equal so it should looks like this:

file_code  file_name       dedicated_hours  sum



0001asder  test-file.docx  4                5

0001as234w asdf.doc        2                2

jgfjh2546  test.docx       4                4

0001asder  test-file.docx  1                5

0001asder  test-file.docx  0                5

0001asder  test-file.docx  0                5

im using sum(dedicated_hours) and group by file_code but it only show:

file_code  file_name       dedicated_hours  sum



0001asder  test-file.docx  4                5

0001as234w asdf.doc        2                2

jgfjh2546  test.docx       4                4

How can i sum and show all registries at the same time?

edited Nov 13 '18 at 16:48

asked Nov 13 '18 at 16:37

user995691

36212

@MartinParkin no it won't work. check again. he does not want to aggregate the results into a single row.

– Madhur Bhaiya
Nov 13 '18 at 16:39

@user995691 What is your MySQL serve version ? Can you upgrade to latest version (8.0.2 and above) ?

– Madhur Bhaiya
Nov 13 '18 at 16:40

2

I'm confused then. How can the SUM be 1 for 3 identical results and 5 for another?

– Martin Parkin
Nov 13 '18 at 16:41

1

@MartinParkin yeah that's a valid question. Expected output does not seem correct.

– Madhur Bhaiya
Nov 13 '18 at 16:42

sorry my bad i edited the output

– user995691
Nov 13 '18 at 16:50

|
show 3 more comments

-1

im trying to sum and show all registries in mysql.
i have this query:

select `deliverables`.*, 

       `users`.`first_name`, 

       `users`.`last_name` 

from `deliverables` 

inner join `users` on `users`.`id` = `deliverables`.`user_id` 

where `deliverables`.`specialty_id` = '11' and 

      `deliverables`.`role_id` <> '1'

and the output is:

file_code  file_name       dedicated_hours



0001asder  test-file.docx  4

0001as234w asdf.doc        2

jgfjh2546  test.docx       4

0001asder  test-file.docx  1

0001asder  test-file.docx  0

0001asder  test-file.docx  0

i need sum the dedicated_hours where file_code is equal so it should looks like this:

file_code  file_name       dedicated_hours  sum



0001asder  test-file.docx  4                5

0001as234w asdf.doc        2                2

jgfjh2546  test.docx       4                4

0001asder  test-file.docx  1                5

0001asder  test-file.docx  0                5

0001asder  test-file.docx  0                5

im using sum(dedicated_hours) and group by file_code but it only show:

file_code  file_name       dedicated_hours  sum



0001asder  test-file.docx  4                5

0001as234w asdf.doc        2                2

jgfjh2546  test.docx       4                4

How can i sum and show all registries at the same time?

edited Nov 13 '18 at 16:48

asked Nov 13 '18 at 16:37

user995691

36212

@MartinParkin no it won't work. check again. he does not want to aggregate the results into a single row.

– Madhur Bhaiya
Nov 13 '18 at 16:39

@user995691 What is your MySQL serve version ? Can you upgrade to latest version (8.0.2 and above) ?

– Madhur Bhaiya
Nov 13 '18 at 16:40

2

I'm confused then. How can the SUM be 1 for 3 identical results and 5 for another?

– Martin Parkin
Nov 13 '18 at 16:41

1

@MartinParkin yeah that's a valid question. Expected output does not seem correct.

– Madhur Bhaiya
Nov 13 '18 at 16:42

sorry my bad i edited the output

– user995691
Nov 13 '18 at 16:50

|
show 3 more comments

-1

im trying to sum and show all registries in mysql.
i have this query:

select `deliverables`.*, 

       `users`.`first_name`, 

       `users`.`last_name` 

from `deliverables` 

inner join `users` on `users`.`id` = `deliverables`.`user_id` 

where `deliverables`.`specialty_id` = '11' and 

      `deliverables`.`role_id` <> '1'

and the output is:

file_code  file_name       dedicated_hours



0001asder  test-file.docx  4

0001as234w asdf.doc        2

jgfjh2546  test.docx       4

0001asder  test-file.docx  1

0001asder  test-file.docx  0

0001asder  test-file.docx  0

i need sum the dedicated_hours where file_code is equal so it should looks like this:

file_code  file_name       dedicated_hours  sum



0001asder  test-file.docx  4                5

0001as234w asdf.doc        2                2

jgfjh2546  test.docx       4                4

0001asder  test-file.docx  1                5

0001asder  test-file.docx  0                5

0001asder  test-file.docx  0                5

im using sum(dedicated_hours) and group by file_code but it only show:

file_code  file_name       dedicated_hours  sum



0001asder  test-file.docx  4                5

0001as234w asdf.doc        2                2

jgfjh2546  test.docx       4                4

How can i sum and show all registries at the same time?

edited Nov 13 '18 at 16:48

asked Nov 13 '18 at 16:37

user995691

36212

im trying to sum and show all registries in mysql.
i have this query:

select `deliverables`.*, 

       `users`.`first_name`, 

       `users`.`last_name` 

from `deliverables` 

inner join `users` on `users`.`id` = `deliverables`.`user_id` 

where `deliverables`.`specialty_id` = '11' and 

      `deliverables`.`role_id` <> '1'

and the output is:

file_code  file_name       dedicated_hours



0001asder  test-file.docx  4

0001as234w asdf.doc        2

jgfjh2546  test.docx       4

0001asder  test-file.docx  1

0001asder  test-file.docx  0

0001asder  test-file.docx  0

i need sum the dedicated_hours where file_code is equal so it should looks like this:

file_code  file_name       dedicated_hours  sum



0001asder  test-file.docx  4                5

0001as234w asdf.doc        2                2

jgfjh2546  test.docx       4                4

0001asder  test-file.docx  1                5

0001asder  test-file.docx  0                5

0001asder  test-file.docx  0                5

im using sum(dedicated_hours) and group by file_code but it only show:

file_code  file_name       dedicated_hours  sum



0001asder  test-file.docx  4                5

0001as234w asdf.doc        2                2

jgfjh2546  test.docx       4                4

How can i sum and show all registries at the same time?

mysql sum

edited Nov 13 '18 at 16:48

asked Nov 13 '18 at 16:37

user995691

36212

edited Nov 13 '18 at 16:48

asked Nov 13 '18 at 16:37

user995691

36212

edited Nov 13 '18 at 16:48

asked Nov 13 '18 at 16:37

user995691

36212

asked Nov 13 '18 at 16:37

user995691

36212

asked Nov 13 '18 at 16:37

user995691

36212

@MartinParkin no it won't work. check again. he does not want to aggregate the results into a single row.

– Madhur Bhaiya
Nov 13 '18 at 16:39

@user995691 What is your MySQL serve version ? Can you upgrade to latest version (8.0.2 and above) ?

– Madhur Bhaiya
Nov 13 '18 at 16:40

2

I'm confused then. How can the SUM be 1 for 3 identical results and 5 for another?

– Martin Parkin
Nov 13 '18 at 16:41

1

@MartinParkin yeah that's a valid question. Expected output does not seem correct.

– Madhur Bhaiya
Nov 13 '18 at 16:42

sorry my bad i edited the output

– user995691
Nov 13 '18 at 16:50

|
show 3 more comments

@MartinParkin no it won't work. check again. he does not want to aggregate the results into a single row.

– Madhur Bhaiya
Nov 13 '18 at 16:39

@user995691 What is your MySQL serve version ? Can you upgrade to latest version (8.0.2 and above) ?

– Madhur Bhaiya
Nov 13 '18 at 16:40

2

I'm confused then. How can the SUM be 1 for 3 identical results and 5 for another?

– Martin Parkin
Nov 13 '18 at 16:41

1

@MartinParkin yeah that's a valid question. Expected output does not seem correct.

– Madhur Bhaiya
Nov 13 '18 at 16:42

sorry my bad i edited the output

– user995691
Nov 13 '18 at 16:50

@MartinParkin no it won't work. check again. he does not want to aggregate the results into a single row.

– Madhur Bhaiya
Nov 13 '18 at 16:39

@user995691 What is your MySQL serve version ? Can you upgrade to latest version (8.0.2 and above) ?

– Madhur Bhaiya
Nov 13 '18 at 16:40

I'm confused then. How can the SUM be 1 for 3 identical results and 5 for another?

– Martin Parkin
Nov 13 '18 at 16:41

@MartinParkin yeah that's a valid question. Expected output does not seem correct.

– Madhur Bhaiya
Nov 13 '18 at 16:42

sorry my bad i edited the output

– user995691
Nov 13 '18 at 16:50

|
show 3 more comments

1 Answer
1

active

oldest

votes

Your MySQL version (5.0.12) does not support Window Functions (it should be a decent incentive enough to upgrade to get the latest goodies :)

Nevertheless, we can use a Derived Table to determine the aggregated SUM() for a file_code separately. Then, we can simply Join back to the main tables, to show the sum column.

SELECT 

  d.file_code,

  d.file_name, 

  d.dedicated_hours,  

  dt.sum, 

  u.first_name, 

  u.last_name 

FROM deliverables AS d 

JOIN users AS u 

  ON u.id = d.user_id 

JOIN

(

  SELECT file_code, 

         SUM(dedicated_hours) AS sum 

   WHERE speciality_id = '11' AND 

         role_id <> '1'

   GROUP BY file_code

) AS dt 

  ON dt.file_code = d.file_code

WHERE d.speciality_id = '11' AND 

      d.role_id <> '1'

MySQL 8.0.2 and above solution would be simply using SUM(..) OVER (..)

SELECT 

  d.file_code,

  d.file_name, 

  d.dedicated_hours,  

  SUM(d.dedicated_hours) OVER (PARTITION BY d.file_code) AS sum, 

  u.first_name, 

  u.last_name 

FROM deliverables AS d 

JOIN users AS u 

  ON u.id = d.user_id 

WHERE d.speciality_id = '11' AND 

      d.role_id <> '1'

answered Nov 13 '18 at 18:17

Madhur Bhaiya

19.5k62236

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');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53285608%2fmysql-sum-and-show-all-registries%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

Your MySQL version (5.0.12) does not support Window Functions (it should be a decent incentive enough to upgrade to get the latest goodies :)

Nevertheless, we can use a Derived Table to determine the aggregated SUM() for a file_code separately. Then, we can simply Join back to the main tables, to show the sum column.

SELECT 

  d.file_code,

  d.file_name, 

  d.dedicated_hours,  

  dt.sum, 

  u.first_name, 

  u.last_name 

FROM deliverables AS d 

JOIN users AS u 

  ON u.id = d.user_id 

JOIN

(

  SELECT file_code, 

         SUM(dedicated_hours) AS sum 

   WHERE speciality_id = '11' AND 

         role_id <> '1'

   GROUP BY file_code

) AS dt 

  ON dt.file_code = d.file_code

WHERE d.speciality_id = '11' AND 

      d.role_id <> '1'

MySQL 8.0.2 and above solution would be simply using SUM(..) OVER (..)

SELECT 

  d.file_code,

  d.file_name, 

  d.dedicated_hours,  

  SUM(d.dedicated_hours) OVER (PARTITION BY d.file_code) AS sum, 

  u.first_name, 

  u.last_name 

FROM deliverables AS d 

JOIN users AS u 

  ON u.id = d.user_id 

WHERE d.speciality_id = '11' AND 

      d.role_id <> '1'

answered Nov 13 '18 at 18:17

Madhur Bhaiya

19.5k62236

add a comment |

Your MySQL version (5.0.12) does not support Window Functions (it should be a decent incentive enough to upgrade to get the latest goodies :)

Nevertheless, we can use a Derived Table to determine the aggregated SUM() for a file_code separately. Then, we can simply Join back to the main tables, to show the sum column.

SELECT 

  d.file_code,

  d.file_name, 

  d.dedicated_hours,  

  dt.sum, 

  u.first_name, 

  u.last_name 

FROM deliverables AS d 

JOIN users AS u 

  ON u.id = d.user_id 

JOIN

(

  SELECT file_code, 

         SUM(dedicated_hours) AS sum 

   WHERE speciality_id = '11' AND 

         role_id <> '1'

   GROUP BY file_code

) AS dt 

  ON dt.file_code = d.file_code

WHERE d.speciality_id = '11' AND 

      d.role_id <> '1'

MySQL 8.0.2 and above solution would be simply using SUM(..) OVER (..)

SELECT 

  d.file_code,

  d.file_name, 

  d.dedicated_hours,  

  SUM(d.dedicated_hours) OVER (PARTITION BY d.file_code) AS sum, 

  u.first_name, 

  u.last_name 

FROM deliverables AS d 

JOIN users AS u 

  ON u.id = d.user_id 

WHERE d.speciality_id = '11' AND 

      d.role_id <> '1'

answered Nov 13 '18 at 18:17

Madhur Bhaiya

19.5k62236

add a comment |

Your MySQL version (5.0.12) does not support Window Functions (it should be a decent incentive enough to upgrade to get the latest goodies :)

Nevertheless, we can use a Derived Table to determine the aggregated SUM() for a file_code separately. Then, we can simply Join back to the main tables, to show the sum column.

SELECT 

  d.file_code,

  d.file_name, 

  d.dedicated_hours,  

  dt.sum, 

  u.first_name, 

  u.last_name 

FROM deliverables AS d 

JOIN users AS u 

  ON u.id = d.user_id 

JOIN

(

  SELECT file_code, 

         SUM(dedicated_hours) AS sum 

   WHERE speciality_id = '11' AND 

         role_id <> '1'

   GROUP BY file_code

) AS dt 

  ON dt.file_code = d.file_code

WHERE d.speciality_id = '11' AND 

      d.role_id <> '1'

MySQL 8.0.2 and above solution would be simply using SUM(..) OVER (..)

SELECT 

  d.file_code,

  d.file_name, 

  d.dedicated_hours,  

  SUM(d.dedicated_hours) OVER (PARTITION BY d.file_code) AS sum, 

  u.first_name, 

  u.last_name 

FROM deliverables AS d 

JOIN users AS u 

  ON u.id = d.user_id 

WHERE d.speciality_id = '11' AND 

      d.role_id <> '1'

answered Nov 13 '18 at 18:17

Madhur Bhaiya

19.5k62236

Your MySQL version (5.0.12) does not support Window Functions (it should be a decent incentive enough to upgrade to get the latest goodies :)

Nevertheless, we can use a Derived Table to determine the aggregated SUM() for a file_code separately. Then, we can simply Join back to the main tables, to show the sum column.

SELECT 

  d.file_code,

  d.file_name, 

  d.dedicated_hours,  

  dt.sum, 

  u.first_name, 

  u.last_name 

FROM deliverables AS d 

JOIN users AS u 

  ON u.id = d.user_id 

JOIN

(

  SELECT file_code, 

         SUM(dedicated_hours) AS sum 

   WHERE speciality_id = '11' AND 

         role_id <> '1'

   GROUP BY file_code

) AS dt 

  ON dt.file_code = d.file_code

WHERE d.speciality_id = '11' AND 

      d.role_id <> '1'

MySQL 8.0.2 and above solution would be simply using SUM(..) OVER (..)

SELECT 

  d.file_code,

  d.file_name, 

  d.dedicated_hours,  

  SUM(d.dedicated_hours) OVER (PARTITION BY d.file_code) AS sum, 

  u.first_name, 

  u.last_name 

FROM deliverables AS d 

JOIN users AS u 

  ON u.id = d.user_id 

WHERE d.speciality_id = '11' AND 

      d.role_id <> '1'

answered Nov 13 '18 at 18:17

Madhur Bhaiya

19.5k62236

answered Nov 13 '18 at 18:17

Madhur Bhaiya

19.5k62236

answered Nov 13 '18 at 18:17

Madhur Bhaiya

19.5k62236

answered Nov 13 '18 at 18:17

Madhur Bhaiya

19.5k62236

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');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Vfrdtyky