How to get different row counts of two different tables in one query with help of group by clause












0















I have one Parent table(Order) and one child table(Item). One user can order multiple orders in one day and one order may have multiple Item.



My table structure like,



Order TableItem table



I would like to get result like, in one how many orders and Items have ordered by particular user.



I need result like



Result



Thank you.










share|improve this question

























  • Image not opening.. Cant help.

    – Krishna
    Nov 22 '18 at 13:40






  • 1





    See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?

    – Strawberry
    Nov 22 '18 at 13:40











  • Can you just mention column names in text for used tables so that we can help.

    – Krishna
    Nov 22 '18 at 13:42











  • please decide about the database system.

    – Barbaros Özhan
    Nov 22 '18 at 15:54
















0















I have one Parent table(Order) and one child table(Item). One user can order multiple orders in one day and one order may have multiple Item.



My table structure like,



Order TableItem table



I would like to get result like, in one how many orders and Items have ordered by particular user.



I need result like



Result



Thank you.










share|improve this question

























  • Image not opening.. Cant help.

    – Krishna
    Nov 22 '18 at 13:40






  • 1





    See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?

    – Strawberry
    Nov 22 '18 at 13:40











  • Can you just mention column names in text for used tables so that we can help.

    – Krishna
    Nov 22 '18 at 13:42











  • please decide about the database system.

    – Barbaros Özhan
    Nov 22 '18 at 15:54














0












0








0








I have one Parent table(Order) and one child table(Item). One user can order multiple orders in one day and one order may have multiple Item.



My table structure like,



Order TableItem table



I would like to get result like, in one how many orders and Items have ordered by particular user.



I need result like



Result



Thank you.










share|improve this question
















I have one Parent table(Order) and one child table(Item). One user can order multiple orders in one day and one order may have multiple Item.



My table structure like,



Order TableItem table



I would like to get result like, in one how many orders and Items have ordered by particular user.



I need result like



Result



Thank you.







mysql database oracle11g database-administration






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 22 '18 at 13:40









Salman A

183k66340434




183k66340434










asked Nov 22 '18 at 13:35









SGKSGK

31




31













  • Image not opening.. Cant help.

    – Krishna
    Nov 22 '18 at 13:40






  • 1





    See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?

    – Strawberry
    Nov 22 '18 at 13:40











  • Can you just mention column names in text for used tables so that we can help.

    – Krishna
    Nov 22 '18 at 13:42











  • please decide about the database system.

    – Barbaros Özhan
    Nov 22 '18 at 15:54



















  • Image not opening.. Cant help.

    – Krishna
    Nov 22 '18 at 13:40






  • 1





    See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?

    – Strawberry
    Nov 22 '18 at 13:40











  • Can you just mention column names in text for used tables so that we can help.

    – Krishna
    Nov 22 '18 at 13:42











  • please decide about the database system.

    – Barbaros Özhan
    Nov 22 '18 at 15:54

















Image not opening.. Cant help.

– Krishna
Nov 22 '18 at 13:40





Image not opening.. Cant help.

– Krishna
Nov 22 '18 at 13:40




1




1





See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?

– Strawberry
Nov 22 '18 at 13:40





See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?

– Strawberry
Nov 22 '18 at 13:40













Can you just mention column names in text for used tables so that we can help.

– Krishna
Nov 22 '18 at 13:42





Can you just mention column names in text for used tables so that we can help.

– Krishna
Nov 22 '18 at 13:42













please decide about the database system.

– Barbaros Özhan
Nov 22 '18 at 15:54





please decide about the database system.

– Barbaros Özhan
Nov 22 '18 at 15:54












2 Answers
2






active

oldest

votes


















0














Try to use distinct count for the count columns, and grouping by user_id is enough as :



select o.user_id as "User Id",
count(distinct o.id_order) as "Count(Order)",
count(distinct i.id_item) as "Count(Item)",
max("date") as "Date"
from orders o
join item i on o.id_order = i.id_order
group by user_id;

User Id Count(Order) Count(Item) Date
------- ------------ ----------- ----------
1 3 7 22.11.2018


Rextester Demo






share|improve this answer
























  • Thanks!!. this works for me. Only I need a data date wise also. So i have removed max(date) and keep it in group by clause.

    – SGK
    Nov 23 '18 at 8:56











  • @swapnil you're welcome friend. Don't forget to mark the answer please.

    – Barbaros Özhan
    Nov 23 '18 at 9:33



















0














You have to use INNER JOIN for user,order and item table. also use GROUP BY clause for group each date and order.



SELECT user_id,
count(o.id_order),
count(item.id_item)
FROM USER
INNER JOIN
`ORDER` o ON user.id=o.id_order
INNER JOIN item i ON o.id_order=o.id_order
GROUP BY o.Date, o.id_order





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%2f53432193%2fhow-to-get-different-row-counts-of-two-different-tables-in-one-query-with-help-o%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














    Try to use distinct count for the count columns, and grouping by user_id is enough as :



    select o.user_id as "User Id",
    count(distinct o.id_order) as "Count(Order)",
    count(distinct i.id_item) as "Count(Item)",
    max("date") as "Date"
    from orders o
    join item i on o.id_order = i.id_order
    group by user_id;

    User Id Count(Order) Count(Item) Date
    ------- ------------ ----------- ----------
    1 3 7 22.11.2018


    Rextester Demo






    share|improve this answer
























    • Thanks!!. this works for me. Only I need a data date wise also. So i have removed max(date) and keep it in group by clause.

      – SGK
      Nov 23 '18 at 8:56











    • @swapnil you're welcome friend. Don't forget to mark the answer please.

      – Barbaros Özhan
      Nov 23 '18 at 9:33
















    0














    Try to use distinct count for the count columns, and grouping by user_id is enough as :



    select o.user_id as "User Id",
    count(distinct o.id_order) as "Count(Order)",
    count(distinct i.id_item) as "Count(Item)",
    max("date") as "Date"
    from orders o
    join item i on o.id_order = i.id_order
    group by user_id;

    User Id Count(Order) Count(Item) Date
    ------- ------------ ----------- ----------
    1 3 7 22.11.2018


    Rextester Demo






    share|improve this answer
























    • Thanks!!. this works for me. Only I need a data date wise also. So i have removed max(date) and keep it in group by clause.

      – SGK
      Nov 23 '18 at 8:56











    • @swapnil you're welcome friend. Don't forget to mark the answer please.

      – Barbaros Özhan
      Nov 23 '18 at 9:33














    0












    0








    0







    Try to use distinct count for the count columns, and grouping by user_id is enough as :



    select o.user_id as "User Id",
    count(distinct o.id_order) as "Count(Order)",
    count(distinct i.id_item) as "Count(Item)",
    max("date") as "Date"
    from orders o
    join item i on o.id_order = i.id_order
    group by user_id;

    User Id Count(Order) Count(Item) Date
    ------- ------------ ----------- ----------
    1 3 7 22.11.2018


    Rextester Demo






    share|improve this answer













    Try to use distinct count for the count columns, and grouping by user_id is enough as :



    select o.user_id as "User Id",
    count(distinct o.id_order) as "Count(Order)",
    count(distinct i.id_item) as "Count(Item)",
    max("date") as "Date"
    from orders o
    join item i on o.id_order = i.id_order
    group by user_id;

    User Id Count(Order) Count(Item) Date
    ------- ------------ ----------- ----------
    1 3 7 22.11.2018


    Rextester Demo







    share|improve this answer












    share|improve this answer



    share|improve this answer










    answered Nov 22 '18 at 16:11









    Barbaros ÖzhanBarbaros Özhan

    13.8k71633




    13.8k71633













    • Thanks!!. this works for me. Only I need a data date wise also. So i have removed max(date) and keep it in group by clause.

      – SGK
      Nov 23 '18 at 8:56











    • @swapnil you're welcome friend. Don't forget to mark the answer please.

      – Barbaros Özhan
      Nov 23 '18 at 9:33



















    • Thanks!!. this works for me. Only I need a data date wise also. So i have removed max(date) and keep it in group by clause.

      – SGK
      Nov 23 '18 at 8:56











    • @swapnil you're welcome friend. Don't forget to mark the answer please.

      – Barbaros Özhan
      Nov 23 '18 at 9:33

















    Thanks!!. this works for me. Only I need a data date wise also. So i have removed max(date) and keep it in group by clause.

    – SGK
    Nov 23 '18 at 8:56





    Thanks!!. this works for me. Only I need a data date wise also. So i have removed max(date) and keep it in group by clause.

    – SGK
    Nov 23 '18 at 8:56













    @swapnil you're welcome friend. Don't forget to mark the answer please.

    – Barbaros Özhan
    Nov 23 '18 at 9:33





    @swapnil you're welcome friend. Don't forget to mark the answer please.

    – Barbaros Özhan
    Nov 23 '18 at 9:33













    0














    You have to use INNER JOIN for user,order and item table. also use GROUP BY clause for group each date and order.



    SELECT user_id,
    count(o.id_order),
    count(item.id_item)
    FROM USER
    INNER JOIN
    `ORDER` o ON user.id=o.id_order
    INNER JOIN item i ON o.id_order=o.id_order
    GROUP BY o.Date, o.id_order





    share|improve this answer






























      0














      You have to use INNER JOIN for user,order and item table. also use GROUP BY clause for group each date and order.



      SELECT user_id,
      count(o.id_order),
      count(item.id_item)
      FROM USER
      INNER JOIN
      `ORDER` o ON user.id=o.id_order
      INNER JOIN item i ON o.id_order=o.id_order
      GROUP BY o.Date, o.id_order





      share|improve this answer




























        0












        0








        0







        You have to use INNER JOIN for user,order and item table. also use GROUP BY clause for group each date and order.



        SELECT user_id,
        count(o.id_order),
        count(item.id_item)
        FROM USER
        INNER JOIN
        `ORDER` o ON user.id=o.id_order
        INNER JOIN item i ON o.id_order=o.id_order
        GROUP BY o.Date, o.id_order





        share|improve this answer















        You have to use INNER JOIN for user,order and item table. also use GROUP BY clause for group each date and order.



        SELECT user_id,
        count(o.id_order),
        count(item.id_item)
        FROM USER
        INNER JOIN
        `ORDER` o ON user.id=o.id_order
        INNER JOIN item i ON o.id_order=o.id_order
        GROUP BY o.Date, o.id_order






        share|improve this answer














        share|improve this answer



        share|improve this answer








        edited Nov 22 '18 at 13:50

























        answered Nov 22 '18 at 13:45









        SadikhasanSadikhasan

        14.1k135994




        14.1k135994






























            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%2f53432193%2fhow-to-get-different-row-counts-of-two-different-tables-in-one-query-with-help-o%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

            "Incorrect syntax near the keyword 'ON'. (on update cascade, on delete cascade,)

            Alcedinidae

            RAC Tourist Trophy