Show and group all records with the same id with two fields that are not null












1















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










share|improve this question

























  • Why did mouseprice = 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











  • 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
















1















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










share|improve this question

























  • Why did mouseprice = 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











  • 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














1












1








1








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










share|improve this question
















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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 13 '18 at 19:24









Brian Tompsett - 汤莱恩

4,1931337101




4,1931337101










asked Nov 13 '18 at 14:12









SylarSylar

277




277













  • Why did mouseprice = 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











  • 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



















  • Why did mouseprice = 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











  • 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

















Why did mouseprice = 50 in your expect result?

– D-Shih
Nov 13 '18 at 14:22





Why did mouseprice = 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












1 Answer
1






active

oldest

votes


















0














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





share|improve this answer























    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


















    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









    0














    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





    share|improve this answer




























      0














      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





      share|improve this answer


























        0












        0








        0







        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





        share|improve this answer













        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






        share|improve this answer












        share|improve this answer



        share|improve this answer










        answered Nov 13 '18 at 14:24









        D-ShihD-Shih

        25.6k61531




        25.6k61531






























            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














            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





















































            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







            Popular posts from this blog

            Bressuire

            Vorschmack

            Quarantine