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,
I would like to get result like, in one how many orders and Items have ordered by particular user.
I need result like
Thank you.
mysql database oracle11g database-administration
edited Nov 22 '18 at 13:40
Salman A
183k66340434
asked Nov 22 '18 at 13:35
SGKSGK
31
add a comment |
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,
I would like to get result like, in one how many orders and Items have ordered by particular user.
I need result like
Thank you.
mysql database oracle11g database-administration
edited Nov 22 '18 at 13:40
Salman A
183k66340434
asked Nov 22 '18 at 13:35
SGKSGK
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
add a comment |
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,
I would like to get result like, in one how many orders and Items have ordered by particular user.
I need result like
Thank you.
mysql database oracle11g database-administration
edited Nov 22 '18 at 13:40
Salman A
183k66340434
asked Nov 22 '18 at 13:35
SGKSGK
31
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,
I would like to get result like, in one how many orders and Items have ordered by particular user.
I need result like
Thank you.
mysql database oracle11g database-administration
mysql database oracle11g database-administration
edited Nov 22 '18 at 13:40
Salman A
183k66340434
asked Nov 22 '18 at 13:35
SGKSGK
31
edited Nov 22 '18 at 13:40
Salman A
183k66340434
asked Nov 22 '18 at 13:35
SGKSGK
31
edited Nov 22 '18 at 13:40
Salman A
183k66340434
edited Nov 22 '18 at 13:40
Salman A
183k66340434
edited Nov 22 '18 at 13:40
Salman A
183k66340434
183k66340434
asked Nov 22 '18 at 13:35
SGKSGK
31
asked Nov 22 '18 at 13:35
SGKSGK
31
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
add a comment |
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
add a comment |
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
answered Nov 22 '18 at 16:11
Barbaros ÖzhanBarbaros Özhan
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
add a comment |
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
answered Nov 22 '18 at 13:45
SadikhasanSadikhasan
14.1k135994
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');
});
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%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
answered Nov 22 '18 at 16:11
Barbaros ÖzhanBarbaros Özhan
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
add a comment |
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
answered Nov 22 '18 at 16:11
Barbaros ÖzhanBarbaros Özhan
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
add a comment |
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
answered Nov 22 '18 at 16:11
Barbaros ÖzhanBarbaros Özhan
13.8k71633
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
answered Nov 22 '18 at 16:11
Barbaros ÖzhanBarbaros Özhan
13.8k71633
answered Nov 22 '18 at 16:11
Barbaros ÖzhanBarbaros Özhan
13.8k71633
answered Nov 22 '18 at 16:11
Barbaros ÖzhanBarbaros Özhan
13.8k71633
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
add a comment |
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
add a comment |
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
answered Nov 22 '18 at 13:45
SadikhasanSadikhasan
14.1k135994
add a comment |
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
answered Nov 22 '18 at 13:45
SadikhasanSadikhasan
14.1k135994
add a comment |
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
answered Nov 22 '18 at 13:45
SadikhasanSadikhasan
14.1k135994
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
answered Nov 22 '18 at 13:45
SadikhasanSadikhasan
14.1k135994
edited Nov 22 '18 at 13:50
answered Nov 22 '18 at 13:45
SadikhasanSadikhasan
14.1k135994
answered Nov 22 '18 at 13:45
SadikhasanSadikhasan
14.1k135994
answered Nov 22 '18 at 13:45
SadikhasanSadikhasan
14.1k135994
14.1k135994
add a comment |
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');
});
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%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
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
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