How to get best items summing duplicates in SQL?

Multi tool use
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
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
add a comment |
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
1
Please provide sample data and desired results, in a tabular text format.
– Gordon Linoff
Jan 3 at 22:07
add a comment |
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
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
php mysql sql mysqli phpmyadmin
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
add a comment |
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
add a comment |
2 Answers
2
active
oldest
votes
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
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 useGROUP BY LOWER(item)
– Nick
Jan 4 at 20:46
add a comment |
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;
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
});
}
});
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%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
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
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 useGROUP BY LOWER(item)
– Nick
Jan 4 at 20:46
add a comment |
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
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 useGROUP BY LOWER(item)
– Nick
Jan 4 at 20:46
add a comment |
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
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
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 useGROUP BY LOWER(item)
– Nick
Jan 4 at 20:46
add a comment |
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 useGROUP 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
add a comment |
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;
add a comment |
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;
add a comment |
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;
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;
edited Jan 3 at 22:33
answered Jan 3 at 22:13
TonyTony
4121312
4121312
add a comment |
add a comment |
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.
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%2f54030441%2fhow-to-get-best-items-summing-duplicates-in-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
DIvUIrM5S,jkiAiBrCT0G8hcl4wh0NgWZIC Kw,LIcMQ8cE3xEn1GuKo3 hz 4bNhCktXeL 7y,0Si hAra5qwWV2h,1o 8kAE8
1
Please provide sample data and desired results, in a tabular text format.
– Gordon Linoff
Jan 3 at 22:07