Get top 10 users data based on the number of sales in My Sql
1
I have table 'sales' and data like this scheme :
User | Amount| Month
user a 100 1
user b 240 1
user c 120 1
user a 200 2
user b 130 2
user c 240 2
How to get TOP 5 user based on Total Sales every month, I've tried using query like this , but there's always showed an error
SELECT TOP10 USER,
SUM(amount) amount
FROM sales LIMIT 10
WHERE MONTH BETWEEN 1 AND 12
GROUP BY sales
ORDER BY 2 DESC
And the result should be :
User a | 300
User b | 370
User c | 360
So the order must be : B,C,A
mysql mysqli
edited Nov 21 '18 at 7:37
Madhur Bhaiya
19.6k62236
asked Nov 21 '18 at 6:55
Riandy EkaRiandy Eka
287
add a comment |
1
I have table 'sales' and data like this scheme :
User | Amount| Month
user a 100 1
user b 240 1
user c 120 1
user a 200 2
user b 130 2
user c 240 2
How to get TOP 5 user based on Total Sales every month, I've tried using query like this , but there's always showed an error
SELECT TOP10 USER,
SUM(amount) amount
FROM sales LIMIT 10
WHERE MONTH BETWEEN 1 AND 12
GROUP BY sales
ORDER BY 2 DESC
And the result should be :
User a | 300
User b | 370
User c | 360
So the order must be : B,C,A
mysql mysqli
edited Nov 21 '18 at 7:37
Madhur Bhaiya
19.6k62236
asked Nov 21 '18 at 6:55
Riandy EkaRiandy Eka
287
2
limit should be after order by,there is no top10 field and if you want to TOP 5 user based on Total Sales then grouping on user might be more effective than grouping by a table. Overall you should review dev.mysql.com/doc/refman/8.0/en/group-by-functions.html, dev.mysql.com/doc/refman/8.0/en/group-by-handling.html, mysqltutorial.org/mysql-limit.aspx
– P.Salmon
Nov 21 '18 at 7:08
1
do you need month wise top 5 users ?
– fa06
Nov 21 '18 at 7:14
1
The question is unclear (because the question as written conflicts with your attempted query)- it would help if you added desired result based on the sample data as text to the question.
– P.Salmon
Nov 21 '18 at 7:24
@fa06 Yes , and it based top sales sum
– Riandy Eka
Nov 21 '18 at 7:34
@P.Salmon Yes, I've adding more description :)
– Riandy Eka
Nov 21 '18 at 7:37
add a comment |
1
1
1
I have table 'sales' and data like this scheme :
User | Amount| Month
user a 100 1
user b 240 1
user c 120 1
user a 200 2
user b 130 2
user c 240 2
How to get TOP 5 user based on Total Sales every month, I've tried using query like this , but there's always showed an error
SELECT TOP10 USER,
SUM(amount) amount
FROM sales LIMIT 10
WHERE MONTH BETWEEN 1 AND 12
GROUP BY sales
ORDER BY 2 DESC
And the result should be :
User a | 300
User b | 370
User c | 360
So the order must be : B,C,A
mysql mysqli
edited Nov 21 '18 at 7:37
Madhur Bhaiya
19.6k62236
asked Nov 21 '18 at 6:55
Riandy EkaRiandy Eka
287
I have table 'sales' and data like this scheme :
User | Amount| Month
user a 100 1
user b 240 1
user c 120 1
user a 200 2
user b 130 2
user c 240 2
How to get TOP 5 user based on Total Sales every month, I've tried using query like this , but there's always showed an error
SELECT TOP10 USER,
SUM(amount) amount
FROM sales LIMIT 10
WHERE MONTH BETWEEN 1 AND 12
GROUP BY sales
ORDER BY 2 DESC
And the result should be :
User a | 300
User b | 370
User c | 360
So the order must be : B,C,A
mysql mysqli
mysql mysqli
edited Nov 21 '18 at 7:37
Madhur Bhaiya
19.6k62236
asked Nov 21 '18 at 6:55
Riandy EkaRiandy Eka
287
edited Nov 21 '18 at 7:37
Madhur Bhaiya
19.6k62236
asked Nov 21 '18 at 6:55
Riandy EkaRiandy Eka
287
edited Nov 21 '18 at 7:37
Madhur Bhaiya
19.6k62236
edited Nov 21 '18 at 7:37
Madhur Bhaiya
19.6k62236
edited Nov 21 '18 at 7:37
Madhur Bhaiya
19.6k62236
19.6k62236
asked Nov 21 '18 at 6:55
Riandy EkaRiandy Eka
287
asked Nov 21 '18 at 6:55
Riandy EkaRiandy Eka
287
asked Nov 21 '18 at 6:55
Riandy EkaRiandy Eka
287
287
2
limit should be after order by,there is no top10 field and if you want to TOP 5 user based on Total Sales then grouping on user might be more effective than grouping by a table. Overall you should review dev.mysql.com/doc/refman/8.0/en/group-by-functions.html, dev.mysql.com/doc/refman/8.0/en/group-by-handling.html, mysqltutorial.org/mysql-limit.aspx
– P.Salmon
Nov 21 '18 at 7:08
1
do you need month wise top 5 users ?
– fa06
Nov 21 '18 at 7:14
1
The question is unclear (because the question as written conflicts with your attempted query)- it would help if you added desired result based on the sample data as text to the question.
– P.Salmon
Nov 21 '18 at 7:24
@fa06 Yes , and it based top sales sum
– Riandy Eka
Nov 21 '18 at 7:34
@P.Salmon Yes, I've adding more description :)
– Riandy Eka
Nov 21 '18 at 7:37
add a comment |
2
limit should be after order by,there is no top10 field and if you want to TOP 5 user based on Total Sales then grouping on user might be more effective than grouping by a table. Overall you should review dev.mysql.com/doc/refman/8.0/en/group-by-functions.html, dev.mysql.com/doc/refman/8.0/en/group-by-handling.html, mysqltutorial.org/mysql-limit.aspx
– P.Salmon
Nov 21 '18 at 7:08
1
do you need month wise top 5 users ?
– fa06
Nov 21 '18 at 7:14
1
The question is unclear (because the question as written conflicts with your attempted query)- it would help if you added desired result based on the sample data as text to the question.
– P.Salmon
Nov 21 '18 at 7:24
@fa06 Yes , and it based top sales sum
– Riandy Eka
Nov 21 '18 at 7:34
@P.Salmon Yes, I've adding more description :)
– Riandy Eka
Nov 21 '18 at 7:37
2
2
limit should be after order by,there is no top10 field and if you want to TOP 5 user based on Total Sales then grouping on user might be more effective than grouping by a table. Overall you should review dev.mysql.com/doc/refman/8.0/en/group-by-functions.html, dev.mysql.com/doc/refman/8.0/en/group-by-handling.html, mysqltutorial.org/mysql-limit.aspx
– P.Salmon
Nov 21 '18 at 7:08
limit should be after order by,there is no top10 field and if you want to TOP 5 user based on Total Sales then grouping on user might be more effective than grouping by a table. Overall you should review dev.mysql.com/doc/refman/8.0/en/group-by-functions.html, dev.mysql.com/doc/refman/8.0/en/group-by-handling.html, mysqltutorial.org/mysql-limit.aspx
– P.Salmon
Nov 21 '18 at 7:08
1
1
do you need month wise top 5 users ?
– fa06
Nov 21 '18 at 7:14
do you need month wise top 5 users ?
– fa06
Nov 21 '18 at 7:14
1
1
The question is unclear (because the question as written conflicts with your attempted query)- it would help if you added desired result based on the sample data as text to the question.
– P.Salmon
Nov 21 '18 at 7:24
The question is unclear (because the question as written conflicts with your attempted query)- it would help if you added desired result based on the sample data as text to the question.
– P.Salmon
Nov 21 '18 at 7:24
@fa06 Yes , and it based top sales sum
– Riandy Eka
Nov 21 '18 at 7:34
@fa06 Yes , and it based top sales sum
– Riandy Eka
Nov 21 '18 at 7:34
@P.Salmon Yes, I've adding more description :)
– Riandy Eka
Nov 21 '18 at 7:37
@P.Salmon Yes, I've adding more description :)
– Riandy Eka
Nov 21 '18 at 7:37
add a comment |
2 Answers
2
active
oldest
votes
1
You can do a GROUP BY on User, and use SUM(amount) to get the total_sales per user. Now, simply sort the result-set by total_sales in Descending order, to get the highest sales first.
We can use LIMIT 10, in case you want to get only Top 10.
SELECT User,
SUM(amount) AS total_sales
FROM sales
WHERE MONTH BETWEEN 1 AND 12
GROUP BY User
ORDER BY total_sales DESC
LIMIT 10
answered Nov 21 '18 at 7:39
Madhur BhaiyaMadhur Bhaiya
19.6k62236
I think this is more relevant I've try it and it works :)
– Riandy Eka
Nov 21 '18 at 7:55
But how if I want to get a data from two tables ?
– Riandy Eka
Nov 28 '18 at 6:19
1
@RiandyEka you can join between the tables using their appropriate Join relationships. However, if it is not clear, you may post another question. You will definitely get the answers there
– Madhur Bhaiya
Nov 28 '18 at 6:20
add a comment |
1
You can try below
SELECT USER, SUM(amount) amount
FROM sales
WHERE MONTH BETWEEN 1 AND 12
GROUP BY USER
ORDER BY amount DESC
answered Nov 21 '18 at 7:40
fa06fa06
16.8k21018
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%2f53406712%2fget-top-10-users-data-based-on-the-number-of-sales-in-my-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
1
You can do a GROUP BY on User, and use SUM(amount) to get the total_sales per user. Now, simply sort the result-set by total_sales in Descending order, to get the highest sales first.
We can use LIMIT 10, in case you want to get only Top 10.
SELECT User,
SUM(amount) AS total_sales
FROM sales
WHERE MONTH BETWEEN 1 AND 12
GROUP BY User
ORDER BY total_sales DESC
LIMIT 10
answered Nov 21 '18 at 7:39
Madhur BhaiyaMadhur Bhaiya
19.6k62236
I think this is more relevant I've try it and it works :)
– Riandy Eka
Nov 21 '18 at 7:55
But how if I want to get a data from two tables ?
– Riandy Eka
Nov 28 '18 at 6:19
1
@RiandyEka you can join between the tables using their appropriate Join relationships. However, if it is not clear, you may post another question. You will definitely get the answers there
– Madhur Bhaiya
Nov 28 '18 at 6:20
add a comment |
1
You can do a GROUP BY on User, and use SUM(amount) to get the total_sales per user. Now, simply sort the result-set by total_sales in Descending order, to get the highest sales first.
We can use LIMIT 10, in case you want to get only Top 10.
SELECT User,
SUM(amount) AS total_sales
FROM sales
WHERE MONTH BETWEEN 1 AND 12
GROUP BY User
ORDER BY total_sales DESC
LIMIT 10
answered Nov 21 '18 at 7:39
Madhur BhaiyaMadhur Bhaiya
19.6k62236
I think this is more relevant I've try it and it works :)
– Riandy Eka
Nov 21 '18 at 7:55
But how if I want to get a data from two tables ?
– Riandy Eka
Nov 28 '18 at 6:19
1
@RiandyEka you can join between the tables using their appropriate Join relationships. However, if it is not clear, you may post another question. You will definitely get the answers there
– Madhur Bhaiya
Nov 28 '18 at 6:20
add a comment |
1
1
1
You can do a GROUP BY on User, and use SUM(amount) to get the total_sales per user. Now, simply sort the result-set by total_sales in Descending order, to get the highest sales first.
We can use LIMIT 10, in case you want to get only Top 10.
SELECT User,
SUM(amount) AS total_sales
FROM sales
WHERE MONTH BETWEEN 1 AND 12
GROUP BY User
ORDER BY total_sales DESC
LIMIT 10
answered Nov 21 '18 at 7:39
Madhur BhaiyaMadhur Bhaiya
19.6k62236
You can do a GROUP BY on User, and use SUM(amount) to get the total_sales per user. Now, simply sort the result-set by total_sales in Descending order, to get the highest sales first.
We can use LIMIT 10, in case you want to get only Top 10.
SELECT User,
SUM(amount) AS total_sales
FROM sales
WHERE MONTH BETWEEN 1 AND 12
GROUP BY User
ORDER BY total_sales DESC
LIMIT 10
answered Nov 21 '18 at 7:39
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 21 '18 at 7:39
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 21 '18 at 7:39
Madhur BhaiyaMadhur Bhaiya
19.6k62236
answered Nov 21 '18 at 7:39
Madhur BhaiyaMadhur Bhaiya
19.6k62236
19.6k62236
I think this is more relevant I've try it and it works :)
– Riandy Eka
Nov 21 '18 at 7:55
But how if I want to get a data from two tables ?
– Riandy Eka
Nov 28 '18 at 6:19
1
@RiandyEka you can join between the tables using their appropriate Join relationships. However, if it is not clear, you may post another question. You will definitely get the answers there
– Madhur Bhaiya
Nov 28 '18 at 6:20
add a comment |
I think this is more relevant I've try it and it works :)
– Riandy Eka
Nov 21 '18 at 7:55
But how if I want to get a data from two tables ?
– Riandy Eka
Nov 28 '18 at 6:19
1
@RiandyEka you can join between the tables using their appropriate Join relationships. However, if it is not clear, you may post another question. You will definitely get the answers there
– Madhur Bhaiya
Nov 28 '18 at 6:20
I think this is more relevant I've try it and it works :)
– Riandy Eka
Nov 21 '18 at 7:55
I think this is more relevant I've try it and it works :)
– Riandy Eka
Nov 21 '18 at 7:55
But how if I want to get a data from two tables ?
– Riandy Eka
Nov 28 '18 at 6:19
But how if I want to get a data from two tables ?
– Riandy Eka
Nov 28 '18 at 6:19
1
1
@RiandyEka you can join between the tables using their appropriate Join relationships. However, if it is not clear, you may post another question. You will definitely get the answers there
– Madhur Bhaiya
Nov 28 '18 at 6:20
@RiandyEka you can join between the tables using their appropriate Join relationships. However, if it is not clear, you may post another question. You will definitely get the answers there
– Madhur Bhaiya
Nov 28 '18 at 6:20
add a comment |
1
You can try below
SELECT USER, SUM(amount) amount
FROM sales
WHERE MONTH BETWEEN 1 AND 12
GROUP BY USER
ORDER BY amount DESC
answered Nov 21 '18 at 7:40
fa06fa06
16.8k21018
add a comment |
1
You can try below
SELECT USER, SUM(amount) amount
FROM sales
WHERE MONTH BETWEEN 1 AND 12
GROUP BY USER
ORDER BY amount DESC
answered Nov 21 '18 at 7:40
fa06fa06
16.8k21018
add a comment |
1
1
1
You can try below
SELECT USER, SUM(amount) amount
FROM sales
WHERE MONTH BETWEEN 1 AND 12
GROUP BY USER
ORDER BY amount DESC
answered Nov 21 '18 at 7:40
fa06fa06
16.8k21018
You can try below
SELECT USER, SUM(amount) amount
FROM sales
WHERE MONTH BETWEEN 1 AND 12
GROUP BY USER
ORDER BY amount DESC
answered Nov 21 '18 at 7:40
fa06fa06
16.8k21018
answered Nov 21 '18 at 7:40
fa06fa06
16.8k21018
answered Nov 21 '18 at 7:40
fa06fa06
16.8k21018
answered Nov 21 '18 at 7:40
fa06fa06
16.8k21018
16.8k21018
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%2f53406712%2fget-top-10-users-data-based-on-the-number-of-sales-in-my-sql%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
2
limit should be after order by,there is no top10 field and if you want to TOP 5 user based on Total Sales then grouping on user might be more effective than grouping by a table. Overall you should review dev.mysql.com/doc/refman/8.0/en/group-by-functions.html, dev.mysql.com/doc/refman/8.0/en/group-by-handling.html, mysqltutorial.org/mysql-limit.aspx
– P.Salmon
Nov 21 '18 at 7:08
1
do you need month wise top 5 users ?
– fa06
Nov 21 '18 at 7:14
1
The question is unclear (because the question as written conflicts with your attempted query)- it would help if you added desired result based on the sample data as text to the question.
– P.Salmon
Nov 21 '18 at 7:24
@fa06 Yes , and it based top sales sum
– Riandy Eka
Nov 21 '18 at 7:34
@P.Salmon Yes, I've adding more description :)
– Riandy Eka
Nov 21 '18 at 7:37