How to get best items summing duplicates in SQL?

Multi tool use
Multi tool use





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}







0















I'm trying to make a list of the best items in the database. An item is better if the profit is higher. The problem is an item a can have duplicates like 500 profit at 10 o'clock and 750 profit at 14 o'clock.



My current code would show both attempts and php I filter this. This way I get the item A once, but I can't tell the total profit of item A.



My SQL statement is this: SELECT item FROM category WHERE endtime >=? ORDER BY profit DESC LIMIT 15

endtime is a unixtime stamp so it shows items from the last week.



And with php, I filter using array_unique($items, SORT_REGULAR);



I would like SQL to give a list of 5 most profitable items with profit and item where duplicates get to add up.



So this: a 500, b 250, c 350, b 100, a 25, c 50
becomes: a 525, c 400, b 350










share|improve this question


















  • 1





    Please provide sample data and desired results, in a tabular text format.

    – Gordon Linoff
    Jan 3 at 22:07




















0















I'm trying to make a list of the best items in the database. An item is better if the profit is higher. The problem is an item a can have duplicates like 500 profit at 10 o'clock and 750 profit at 14 o'clock.



My current code would show both attempts and php I filter this. This way I get the item A once, but I can't tell the total profit of item A.



My SQL statement is this: SELECT item FROM category WHERE endtime >=? ORDER BY profit DESC LIMIT 15

endtime is a unixtime stamp so it shows items from the last week.



And with php, I filter using array_unique($items, SORT_REGULAR);



I would like SQL to give a list of 5 most profitable items with profit and item where duplicates get to add up.



So this: a 500, b 250, c 350, b 100, a 25, c 50
becomes: a 525, c 400, b 350










share|improve this question


















  • 1





    Please provide sample data and desired results, in a tabular text format.

    – Gordon Linoff
    Jan 3 at 22:07
















0












0








0








I'm trying to make a list of the best items in the database. An item is better if the profit is higher. The problem is an item a can have duplicates like 500 profit at 10 o'clock and 750 profit at 14 o'clock.



My current code would show both attempts and php I filter this. This way I get the item A once, but I can't tell the total profit of item A.



My SQL statement is this: SELECT item FROM category WHERE endtime >=? ORDER BY profit DESC LIMIT 15

endtime is a unixtime stamp so it shows items from the last week.



And with php, I filter using array_unique($items, SORT_REGULAR);



I would like SQL to give a list of 5 most profitable items with profit and item where duplicates get to add up.



So this: a 500, b 250, c 350, b 100, a 25, c 50
becomes: a 525, c 400, b 350










share|improve this question














I'm trying to make a list of the best items in the database. An item is better if the profit is higher. The problem is an item a can have duplicates like 500 profit at 10 o'clock and 750 profit at 14 o'clock.



My current code would show both attempts and php I filter this. This way I get the item A once, but I can't tell the total profit of item A.



My SQL statement is this: SELECT item FROM category WHERE endtime >=? ORDER BY profit DESC LIMIT 15

endtime is a unixtime stamp so it shows items from the last week.



And with php, I filter using array_unique($items, SORT_REGULAR);



I would like SQL to give a list of 5 most profitable items with profit and item where duplicates get to add up.



So this: a 500, b 250, c 350, b 100, a 25, c 50
becomes: a 525, c 400, b 350







php mysql sql mysqli phpmyadmin






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Jan 3 at 22:05









HelperHelper

82




82








  • 1





    Please provide sample data and desired results, in a tabular text format.

    – Gordon Linoff
    Jan 3 at 22:07
















  • 1





    Please provide sample data and desired results, in a tabular text format.

    – Gordon Linoff
    Jan 3 at 22:07










1




1





Please provide sample data and desired results, in a tabular text format.

– Gordon Linoff
Jan 3 at 22:07







Please provide sample data and desired results, in a tabular text format.

– Gordon Linoff
Jan 3 at 22:07














2 Answers
2






active

oldest

votes


















0














This query should give you the result you want (without sample data it's hard to be certain). Basically you need to use SUM to sum the profit and GROUP BY item to get sums for each different item.



SELECT item, SUM(profit) AS total_profit 
FROM category
WHERE endtime >=?
GROUP BY item
ORDER BY total_profit DESC
LIMIT 5





share|improve this answer


























  • Hey Nick, if you think that I copied your answer, I did not. It could've been possible that I would've posted before you and you had the same query as mine, as 2 correct answers can be similar, and we're also using columns from the original question and existing functions in Mysql. But if you meant a similar query shouldn't have been posted again, I have already deleted my answer.

    – manishk
    Jan 3 at 23:24








  • 1





    @manishk had you posted before me, I would not have posted my answer. Having two users post essentially identical answers does not help anyone.

    – Nick
    Jan 3 at 23:27













  • This is exactly what I expect. The Group By was the keyword I was forgotten. Just to be sure. If item shoes_red_size_43 get entered as shoes_red_Size_43 they will count as different items?

    – Helper
    Jan 4 at 15:46











  • Yes they would be counted as different items. If you want to avoid that you can use GROUP BY LOWER(item)

    – Nick
    Jan 4 at 20:46



















0














If i understood correctly you have a table given like this:



item | profit
-----------
a | 500
b | 250
c | 350
b | 100
a | 25
c | 50


and you want:



item | profit
-----------
a | 525
b | 350
c | 400


This can be achieved quite easily using GROUP BY and SUM



SELECT item, sum(profit)
FROM category
WHERE endtime >=?
GROUP BY id;





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%2f54030441%2fhow-to-get-best-items-summing-duplicates-in-sql%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









    0














    This query should give you the result you want (without sample data it's hard to be certain). Basically you need to use SUM to sum the profit and GROUP BY item to get sums for each different item.



    SELECT item, SUM(profit) AS total_profit 
    FROM category
    WHERE endtime >=?
    GROUP BY item
    ORDER BY total_profit DESC
    LIMIT 5





    share|improve this answer


























    • Hey Nick, if you think that I copied your answer, I did not. It could've been possible that I would've posted before you and you had the same query as mine, as 2 correct answers can be similar, and we're also using columns from the original question and existing functions in Mysql. But if you meant a similar query shouldn't have been posted again, I have already deleted my answer.

      – manishk
      Jan 3 at 23:24








    • 1





      @manishk had you posted before me, I would not have posted my answer. Having two users post essentially identical answers does not help anyone.

      – Nick
      Jan 3 at 23:27













    • This is exactly what I expect. The Group By was the keyword I was forgotten. Just to be sure. If item shoes_red_size_43 get entered as shoes_red_Size_43 they will count as different items?

      – Helper
      Jan 4 at 15:46











    • Yes they would be counted as different items. If you want to avoid that you can use GROUP BY LOWER(item)

      – Nick
      Jan 4 at 20:46
















    0














    This query should give you the result you want (without sample data it's hard to be certain). Basically you need to use SUM to sum the profit and GROUP BY item to get sums for each different item.



    SELECT item, SUM(profit) AS total_profit 
    FROM category
    WHERE endtime >=?
    GROUP BY item
    ORDER BY total_profit DESC
    LIMIT 5





    share|improve this answer


























    • Hey Nick, if you think that I copied your answer, I did not. It could've been possible that I would've posted before you and you had the same query as mine, as 2 correct answers can be similar, and we're also using columns from the original question and existing functions in Mysql. But if you meant a similar query shouldn't have been posted again, I have already deleted my answer.

      – manishk
      Jan 3 at 23:24








    • 1





      @manishk had you posted before me, I would not have posted my answer. Having two users post essentially identical answers does not help anyone.

      – Nick
      Jan 3 at 23:27













    • This is exactly what I expect. The Group By was the keyword I was forgotten. Just to be sure. If item shoes_red_size_43 get entered as shoes_red_Size_43 they will count as different items?

      – Helper
      Jan 4 at 15:46











    • Yes they would be counted as different items. If you want to avoid that you can use GROUP BY LOWER(item)

      – Nick
      Jan 4 at 20:46














    0












    0








    0







    This query should give you the result you want (without sample data it's hard to be certain). Basically you need to use SUM to sum the profit and GROUP BY item to get sums for each different item.



    SELECT item, SUM(profit) AS total_profit 
    FROM category
    WHERE endtime >=?
    GROUP BY item
    ORDER BY total_profit DESC
    LIMIT 5





    share|improve this answer















    This query should give you the result you want (without sample data it's hard to be certain). Basically you need to use SUM to sum the profit and GROUP BY item to get sums for each different item.



    SELECT item, SUM(profit) AS total_profit 
    FROM category
    WHERE endtime >=?
    GROUP BY item
    ORDER BY total_profit DESC
    LIMIT 5






    share|improve this answer














    share|improve this answer



    share|improve this answer








    edited Jan 4 at 3:04

























    answered Jan 3 at 22:15









    NickNick

    38.7k132443




    38.7k132443













    • Hey Nick, if you think that I copied your answer, I did not. It could've been possible that I would've posted before you and you had the same query as mine, as 2 correct answers can be similar, and we're also using columns from the original question and existing functions in Mysql. But if you meant a similar query shouldn't have been posted again, I have already deleted my answer.

      – manishk
      Jan 3 at 23:24








    • 1





      @manishk had you posted before me, I would not have posted my answer. Having two users post essentially identical answers does not help anyone.

      – Nick
      Jan 3 at 23:27













    • This is exactly what I expect. The Group By was the keyword I was forgotten. Just to be sure. If item shoes_red_size_43 get entered as shoes_red_Size_43 they will count as different items?

      – Helper
      Jan 4 at 15:46











    • Yes they would be counted as different items. If you want to avoid that you can use GROUP BY LOWER(item)

      – Nick
      Jan 4 at 20:46



















    • Hey Nick, if you think that I copied your answer, I did not. It could've been possible that I would've posted before you and you had the same query as mine, as 2 correct answers can be similar, and we're also using columns from the original question and existing functions in Mysql. But if you meant a similar query shouldn't have been posted again, I have already deleted my answer.

      – manishk
      Jan 3 at 23:24








    • 1





      @manishk had you posted before me, I would not have posted my answer. Having two users post essentially identical answers does not help anyone.

      – Nick
      Jan 3 at 23:27













    • This is exactly what I expect. The Group By was the keyword I was forgotten. Just to be sure. If item shoes_red_size_43 get entered as shoes_red_Size_43 they will count as different items?

      – Helper
      Jan 4 at 15:46











    • Yes they would be counted as different items. If you want to avoid that you can use GROUP BY LOWER(item)

      – Nick
      Jan 4 at 20:46

















    Hey Nick, if you think that I copied your answer, I did not. It could've been possible that I would've posted before you and you had the same query as mine, as 2 correct answers can be similar, and we're also using columns from the original question and existing functions in Mysql. But if you meant a similar query shouldn't have been posted again, I have already deleted my answer.

    – manishk
    Jan 3 at 23:24







    Hey Nick, if you think that I copied your answer, I did not. It could've been possible that I would've posted before you and you had the same query as mine, as 2 correct answers can be similar, and we're also using columns from the original question and existing functions in Mysql. But if you meant a similar query shouldn't have been posted again, I have already deleted my answer.

    – manishk
    Jan 3 at 23:24






    1




    1





    @manishk had you posted before me, I would not have posted my answer. Having two users post essentially identical answers does not help anyone.

    – Nick
    Jan 3 at 23:27







    @manishk had you posted before me, I would not have posted my answer. Having two users post essentially identical answers does not help anyone.

    – Nick
    Jan 3 at 23:27















    This is exactly what I expect. The Group By was the keyword I was forgotten. Just to be sure. If item shoes_red_size_43 get entered as shoes_red_Size_43 they will count as different items?

    – Helper
    Jan 4 at 15:46





    This is exactly what I expect. The Group By was the keyword I was forgotten. Just to be sure. If item shoes_red_size_43 get entered as shoes_red_Size_43 they will count as different items?

    – Helper
    Jan 4 at 15:46













    Yes they would be counted as different items. If you want to avoid that you can use GROUP BY LOWER(item)

    – Nick
    Jan 4 at 20:46





    Yes they would be counted as different items. If you want to avoid that you can use GROUP BY LOWER(item)

    – Nick
    Jan 4 at 20:46













    0














    If i understood correctly you have a table given like this:



    item | profit
    -----------
    a | 500
    b | 250
    c | 350
    b | 100
    a | 25
    c | 50


    and you want:



    item | profit
    -----------
    a | 525
    b | 350
    c | 400


    This can be achieved quite easily using GROUP BY and SUM



    SELECT item, sum(profit)
    FROM category
    WHERE endtime >=?
    GROUP BY id;





    share|improve this answer






























      0














      If i understood correctly you have a table given like this:



      item | profit
      -----------
      a | 500
      b | 250
      c | 350
      b | 100
      a | 25
      c | 50


      and you want:



      item | profit
      -----------
      a | 525
      b | 350
      c | 400


      This can be achieved quite easily using GROUP BY and SUM



      SELECT item, sum(profit)
      FROM category
      WHERE endtime >=?
      GROUP BY id;





      share|improve this answer




























        0












        0








        0







        If i understood correctly you have a table given like this:



        item | profit
        -----------
        a | 500
        b | 250
        c | 350
        b | 100
        a | 25
        c | 50


        and you want:



        item | profit
        -----------
        a | 525
        b | 350
        c | 400


        This can be achieved quite easily using GROUP BY and SUM



        SELECT item, sum(profit)
        FROM category
        WHERE endtime >=?
        GROUP BY id;





        share|improve this answer















        If i understood correctly you have a table given like this:



        item | profit
        -----------
        a | 500
        b | 250
        c | 350
        b | 100
        a | 25
        c | 50


        and you want:



        item | profit
        -----------
        a | 525
        b | 350
        c | 400


        This can be achieved quite easily using GROUP BY and SUM



        SELECT item, sum(profit)
        FROM category
        WHERE endtime >=?
        GROUP BY id;






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Jan 3 at 22:33

























        answered Jan 3 at 22:13









        TonyTony

        4121312




        4121312






























            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%2f54030441%2fhow-to-get-best-items-summing-duplicates-in-sql%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







            DIvUIrM5S,jkiAiBrCT0G8hcl4wh0NgWZIC Kw,LIcMQ8cE3xEn1GuKo3 hz 4bNhCktXeL 7y,0Si hAra5qwWV2h,1o 8kAE8
            js9a,S2rynY lETV

            Popular posts from this blog

            Monofisismo

            Angular Downloading a file using contenturl with Basic Authentication

            Olmecas