Show and group all records with the same id with two fields that are not null
I have a table that shows all the products, the price_fact field, calculates the average when invoiced and updates the price_fact field when it makes an insert, and the price_alb field is the same but with a delivery note.
So when invoice (price_fact) has no delivery note (price_alb) and when it is delivery note has no invoice.
+---------------+------------+-------+-------------+-----------+
| name | id_product | price | price_fact| price_alb|
+---------------+------------+-------+-------------+-----------+
| phone | 1 | 300 | NULL | 275|
| mouse | 2 | 100 | 100 | NULL|
| phone | 1 | 250 | NULL | 275|
| mouse | 2 | 100 | NULL | 100|
| phone | 1 | 300 | 300 | NULL|
+---------------+------------+------+--------------+-----------+
And what I want is to calculate all the products in a single row, the number of products in total and the final result shows the column price_fact and price_alb of that product, of those who have had records that are not null.
+---------------+------------+-------+-------------+-----------+------+
| name | id_product | price | price_fact| price_alb| units|
+---------------+------------+-------+-------------+-----------+------+
| phone | 1 | 300 | 300 | 275| 3|
| mouse | 2 | 100 | 100 | 100| 2|
+---------------+------------+------+--------------+-----------+------+
Solved (I have put MAX(price_fact), MAX(price_alb) in select).
mysql select
edited Nov 13 '18 at 19:24
Brian Tompsett - 汤莱恩
4,2031338101
asked Nov 13 '18 at 14:12
SylarSylar
277
add a comment |
I have a table that shows all the products, the price_fact field, calculates the average when invoiced and updates the price_fact field when it makes an insert, and the price_alb field is the same but with a delivery note.
So when invoice (price_fact) has no delivery note (price_alb) and when it is delivery note has no invoice.
+---------------+------------+-------+-------------+-----------+
| name | id_product | price | price_fact| price_alb|
+---------------+------------+-------+-------------+-----------+
| phone | 1 | 300 | NULL | 275|
| mouse | 2 | 100 | 100 | NULL|
| phone | 1 | 250 | NULL | 275|
| mouse | 2 | 100 | NULL | 100|
| phone | 1 | 300 | 300 | NULL|
+---------------+------------+------+--------------+-----------+
And what I want is to calculate all the products in a single row, the number of products in total and the final result shows the column price_fact and price_alb of that product, of those who have had records that are not null.
+---------------+------------+-------+-------------+-----------+------+
| name | id_product | price | price_fact| price_alb| units|
+---------------+------------+-------+-------------+-----------+------+
| phone | 1 | 300 | 300 | 275| 3|
| mouse | 2 | 100 | 100 | 100| 2|
+---------------+------------+------+--------------+-----------+------+
Solved (I have put MAX(price_fact), MAX(price_alb) in select).
mysql select
edited Nov 13 '18 at 19:24
Brian Tompsett - 汤莱恩
4,2031338101
asked Nov 13 '18 at 14:12
SylarSylar
277
Why did mouseprice = 50in your expect result?
– D-Shih
Nov 13 '18 at 14:22
I have the solution now, only put MAX(price_alb), MAX(price_fact).
– Sylar
Nov 13 '18 at 14:27
But I am curious how can do you getprice = 50instead of 100
– D-Shih
Nov 13 '18 at 14:29
I sorry I have calculated wrong when I have written. It's 100.
– Sylar
Nov 13 '18 at 14:30
add a comment |
I have a table that shows all the products, the price_fact field, calculates the average when invoiced and updates the price_fact field when it makes an insert, and the price_alb field is the same but with a delivery note.
So when invoice (price_fact) has no delivery note (price_alb) and when it is delivery note has no invoice.
+---------------+------------+-------+-------------+-----------+
| name | id_product | price | price_fact| price_alb|
+---------------+------------+-------+-------------+-----------+
| phone | 1 | 300 | NULL | 275|
| mouse | 2 | 100 | 100 | NULL|
| phone | 1 | 250 | NULL | 275|
| mouse | 2 | 100 | NULL | 100|
| phone | 1 | 300 | 300 | NULL|
+---------------+------------+------+--------------+-----------+
And what I want is to calculate all the products in a single row, the number of products in total and the final result shows the column price_fact and price_alb of that product, of those who have had records that are not null.
+---------------+------------+-------+-------------+-----------+------+
| name | id_product | price | price_fact| price_alb| units|
+---------------+------------+-------+-------------+-----------+------+
| phone | 1 | 300 | 300 | 275| 3|
| mouse | 2 | 100 | 100 | 100| 2|
+---------------+------------+------+--------------+-----------+------+
Solved (I have put MAX(price_fact), MAX(price_alb) in select).
mysql select
edited Nov 13 '18 at 19:24
Brian Tompsett - 汤莱恩
4,2031338101
asked Nov 13 '18 at 14:12
SylarSylar
277
I have a table that shows all the products, the price_fact field, calculates the average when invoiced and updates the price_fact field when it makes an insert, and the price_alb field is the same but with a delivery note.
So when invoice (price_fact) has no delivery note (price_alb) and when it is delivery note has no invoice.
+---------------+------------+-------+-------------+-----------+
| name | id_product | price | price_fact| price_alb|
+---------------+------------+-------+-------------+-----------+
| phone | 1 | 300 | NULL | 275|
| mouse | 2 | 100 | 100 | NULL|
| phone | 1 | 250 | NULL | 275|
| mouse | 2 | 100 | NULL | 100|
| phone | 1 | 300 | 300 | NULL|
+---------------+------------+------+--------------+-----------+
And what I want is to calculate all the products in a single row, the number of products in total and the final result shows the column price_fact and price_alb of that product, of those who have had records that are not null.
+---------------+------------+-------+-------------+-----------+------+
| name | id_product | price | price_fact| price_alb| units|
+---------------+------------+-------+-------------+-----------+------+
| phone | 1 | 300 | 300 | 275| 3|
| mouse | 2 | 100 | 100 | 100| 2|
+---------------+------------+------+--------------+-----------+------+
Solved (I have put MAX(price_fact), MAX(price_alb) in select).
mysql select
mysql select
edited Nov 13 '18 at 19:24
Brian Tompsett - 汤莱恩
4,2031338101
asked Nov 13 '18 at 14:12
SylarSylar
277
edited Nov 13 '18 at 19:24
Brian Tompsett - 汤莱恩
4,2031338101
asked Nov 13 '18 at 14:12
SylarSylar
277
edited Nov 13 '18 at 19:24
Brian Tompsett - 汤莱恩
4,2031338101
edited Nov 13 '18 at 19:24
Brian Tompsett - 汤莱恩
4,2031338101
edited Nov 13 '18 at 19:24
Brian Tompsett - 汤莱恩
4,2031338101
4,2031338101
asked Nov 13 '18 at 14:12
SylarSylar
277
asked Nov 13 '18 at 14:12
SylarSylar
277
asked Nov 13 '18 at 14:12
SylarSylar
277
277
Why did mouseprice = 50in your expect result?
– D-Shih
Nov 13 '18 at 14:22
I have the solution now, only put MAX(price_alb), MAX(price_fact).
– Sylar
Nov 13 '18 at 14:27
But I am curious how can do you getprice = 50instead of 100
– D-Shih
Nov 13 '18 at 14:29
I sorry I have calculated wrong when I have written. It's 100.
– Sylar
Nov 13 '18 at 14:30
add a comment |
Why did mouseprice = 50in your expect result?
– D-Shih
Nov 13 '18 at 14:22
I have the solution now, only put MAX(price_alb), MAX(price_fact).
– Sylar
Nov 13 '18 at 14:27
But I am curious how can do you getprice = 50instead of 100
– D-Shih
Nov 13 '18 at 14:29
I sorry I have calculated wrong when I have written. It's 100.
– Sylar
Nov 13 '18 at 14:30
Why did mouse
price = 50 in your expect result?– D-Shih
Nov 13 '18 at 14:22
Why did mouse
price = 50 in your expect result?– D-Shih
Nov 13 '18 at 14:22
I have the solution now, only put MAX(price_alb), MAX(price_fact).
– Sylar
Nov 13 '18 at 14:27
I have the solution now, only put MAX(price_alb), MAX(price_fact).
– Sylar
Nov 13 '18 at 14:27
But I am curious how can do you get
price = 50 instead of 100– D-Shih
Nov 13 '18 at 14:29
But I am curious how can do you get
price = 50 instead of 100– D-Shih
Nov 13 '18 at 14:29
I sorry I have calculated wrong when I have written. It's 100.
– Sylar
Nov 13 '18 at 14:30
I sorry I have calculated wrong when I have written. It's 100.
– Sylar
Nov 13 '18 at 14:30
add a comment |
1 Answer
1
active
oldest
votes
You can try to use aggregate function.
SELECT name,
id_product,
MAX(price) price,
MAX(price_fact) price_fact,
MAX(price_alb) price_alb,
COUNT(*) units
FROM T
GROUP BY name,id_product
answered Nov 13 '18 at 14:24
D-ShihD-Shih
25.8k61531
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%2f53282937%2fshow-and-group-all-records-with-the-same-id-with-two-fields-that-are-not-null%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 to use aggregate function.
SELECT name,
id_product,
MAX(price) price,
MAX(price_fact) price_fact,
MAX(price_alb) price_alb,
COUNT(*) units
FROM T
GROUP BY name,id_product
answered Nov 13 '18 at 14:24
D-ShihD-Shih
25.8k61531
add a comment |
You can try to use aggregate function.
SELECT name,
id_product,
MAX(price) price,
MAX(price_fact) price_fact,
MAX(price_alb) price_alb,
COUNT(*) units
FROM T
GROUP BY name,id_product
answered Nov 13 '18 at 14:24
D-ShihD-Shih
25.8k61531
add a comment |
You can try to use aggregate function.
SELECT name,
id_product,
MAX(price) price,
MAX(price_fact) price_fact,
MAX(price_alb) price_alb,
COUNT(*) units
FROM T
GROUP BY name,id_product
answered Nov 13 '18 at 14:24
D-ShihD-Shih
25.8k61531
You can try to use aggregate function.
SELECT name,
id_product,
MAX(price) price,
MAX(price_fact) price_fact,
MAX(price_alb) price_alb,
COUNT(*) units
FROM T
GROUP BY name,id_product
answered Nov 13 '18 at 14:24
D-ShihD-Shih
25.8k61531
answered Nov 13 '18 at 14:24
D-ShihD-Shih
25.8k61531
answered Nov 13 '18 at 14:24
D-ShihD-Shih
25.8k61531
answered Nov 13 '18 at 14:24
D-ShihD-Shih
25.8k61531
25.8k61531
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%2f53282937%2fshow-and-group-all-records-with-the-same-id-with-two-fields-that-are-not-null%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
Why did mouse
price = 50in your expect result?– D-Shih
Nov 13 '18 at 14:22
I have the solution now, only put MAX(price_alb), MAX(price_fact).
– Sylar
Nov 13 '18 at 14:27
But I am curious how can do you get
price = 50instead of 100– D-Shih
Nov 13 '18 at 14:29
I sorry I have calculated wrong when I have written. It's 100.
– Sylar
Nov 13 '18 at 14:30