Group by with conditions
I have to display some data in php the structure of data is like this
id channel_id added_by
1 1 9999
2 1 195
3 8 9999
4 1 180
5 1 195
6 8 9999
7 1 9999
8 1 195
9 8 9999
I need to show total count of channel id = 1 only like this
channel_id total added_by
1 2 9999
1 4 -1
Its like count of all channel id which are added by 9999 and all other added are assumed as (-1)
I am able to do it like
SELECT channel_id, added_by, sum(id) as total
from table
where channel_id = 1
group by channel_id ,added_by;
but it gives following result which is not as I need
channel_id added_by total
1 9999 2
1 195 3
1 180 1
Please help or give a hint any help is appreciated
php mysql
edited Nov 12 '18 at 14:15
Brijesh Kumar Tripathi
1439
asked Nov 12 '18 at 13:01
user1234user1234
4,3221724
add a comment |
I have to display some data in php the structure of data is like this
id channel_id added_by
1 1 9999
2 1 195
3 8 9999
4 1 180
5 1 195
6 8 9999
7 1 9999
8 1 195
9 8 9999
I need to show total count of channel id = 1 only like this
channel_id total added_by
1 2 9999
1 4 -1
Its like count of all channel id which are added by 9999 and all other added are assumed as (-1)
I am able to do it like
SELECT channel_id, added_by, sum(id) as total
from table
where channel_id = 1
group by channel_id ,added_by;
but it gives following result which is not as I need
channel_id added_by total
1 9999 2
1 195 3
1 180 1
Please help or give a hint any help is appreciated
php mysql
edited Nov 12 '18 at 14:15
Brijesh Kumar Tripathi
1439
asked Nov 12 '18 at 13:01
user1234user1234
4,3221724
add a comment |
I have to display some data in php the structure of data is like this
id channel_id added_by
1 1 9999
2 1 195
3 8 9999
4 1 180
5 1 195
6 8 9999
7 1 9999
8 1 195
9 8 9999
I need to show total count of channel id = 1 only like this
channel_id total added_by
1 2 9999
1 4 -1
Its like count of all channel id which are added by 9999 and all other added are assumed as (-1)
I am able to do it like
SELECT channel_id, added_by, sum(id) as total
from table
where channel_id = 1
group by channel_id ,added_by;
but it gives following result which is not as I need
channel_id added_by total
1 9999 2
1 195 3
1 180 1
Please help or give a hint any help is appreciated
php mysql
edited Nov 12 '18 at 14:15
Brijesh Kumar Tripathi
1439
asked Nov 12 '18 at 13:01
user1234user1234
4,3221724
I have to display some data in php the structure of data is like this
id channel_id added_by
1 1 9999
2 1 195
3 8 9999
4 1 180
5 1 195
6 8 9999
7 1 9999
8 1 195
9 8 9999
I need to show total count of channel id = 1 only like this
channel_id total added_by
1 2 9999
1 4 -1
Its like count of all channel id which are added by 9999 and all other added are assumed as (-1)
I am able to do it like
SELECT channel_id, added_by, sum(id) as total
from table
where channel_id = 1
group by channel_id ,added_by;
but it gives following result which is not as I need
channel_id added_by total
1 9999 2
1 195 3
1 180 1
Please help or give a hint any help is appreciated
php mysql
php mysql
edited Nov 12 '18 at 14:15
Brijesh Kumar Tripathi
1439
asked Nov 12 '18 at 13:01
user1234user1234
4,3221724
edited Nov 12 '18 at 14:15
Brijesh Kumar Tripathi
1439
asked Nov 12 '18 at 13:01
user1234user1234
4,3221724
edited Nov 12 '18 at 14:15
Brijesh Kumar Tripathi
1439
edited Nov 12 '18 at 14:15
Brijesh Kumar Tripathi
1439
edited Nov 12 '18 at 14:15
Brijesh Kumar Tripathi
1439
1439
asked Nov 12 '18 at 13:01
user1234user1234
4,3221724
asked Nov 12 '18 at 13:01
user1234user1234
4,3221724
asked Nov 12 '18 at 13:01
user1234user1234
4,3221724
4,3221724
add a comment |
add a comment |
4 Answers
4
active
oldest
votes
Using CASE..WHEN expressions, you can determine a "modified" added_by value, and GROUP BY on the modified value instead.
Also, SUM(id) will not give you correct count of rows. It would rather add up the id values. You can use COUNT(*) instead to count the number of rows:
SELECT channel_id,
CASE
WHEN added_by <> 9999 THEN -1
ELSE 9999
END AS modified_added_by,
COUNT(*) as total
FROM your_table_name
WHERE channel_id = 1
GROUP BY channel_id, modified_added_by
Result
| channel_id | modified_added_by | total |
| ---------- | ----------------- | ----- |
| 1 | -1 | 4 |
| 1 | 9999 | 2 |
View on DB Fiddle
answered Nov 12 '18 at 13:03
Madhur BhaiyaMadhur Bhaiya
19.5k62236
add a comment |
http://sqlfiddle.com/#!9/437406/2
SELECT channel_id,
IF(added_by=9999,9999,-1) new_col,
COUNT(*)
FROM `table`
GROUP BY channel_id, new_col
answered Nov 12 '18 at 14:14
AlexAlex
14.3k11836
add a comment |
You can try this:
SELECT channel_id,
if(9999,9999,-1) as added_by,
sum(id) as total
from table
where channel_id = 1
group by channel_id,added_by;
edited Nov 12 '18 at 13:21
Francesco Boi
1,58421935
answered Nov 12 '18 at 13:08
user2485245user2485245
11
add a comment |
Hope this will help you.
SELECT count(*) as total, channel_id, added_by from table group by channel_id, added_by;
answered Nov 12 '18 at 13:21
Dharmik RavalDharmik Raval
15
How it will generate -1 in added_by column.
– Brijesh Kumar Tripathi
Nov 12 '18 at 13:34
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%2f53262733%2fgroup-by-with-conditions%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
4 Answers
4
active
oldest
votes
4 Answers
4
active
oldest
votes
active
oldest
votes
active
oldest
votes
Using CASE..WHEN expressions, you can determine a "modified" added_by value, and GROUP BY on the modified value instead.
Also, SUM(id) will not give you correct count of rows. It would rather add up the id values. You can use COUNT(*) instead to count the number of rows:
SELECT channel_id,
CASE
WHEN added_by <> 9999 THEN -1
ELSE 9999
END AS modified_added_by,
COUNT(*) as total
FROM your_table_name
WHERE channel_id = 1
GROUP BY channel_id, modified_added_by
Result
| channel_id | modified_added_by | total |
| ---------- | ----------------- | ----- |
| 1 | -1 | 4 |
| 1 | 9999 | 2 |
View on DB Fiddle
answered Nov 12 '18 at 13:03
Madhur BhaiyaMadhur Bhaiya
19.5k62236
add a comment |
Using CASE..WHEN expressions, you can determine a "modified" added_by value, and GROUP BY on the modified value instead.
Also, SUM(id) will not give you correct count of rows. It would rather add up the id values. You can use COUNT(*) instead to count the number of rows:
SELECT channel_id,
CASE
WHEN added_by <> 9999 THEN -1
ELSE 9999
END AS modified_added_by,
COUNT(*) as total
FROM your_table_name
WHERE channel_id = 1
GROUP BY channel_id, modified_added_by
Result
| channel_id | modified_added_by | total |
| ---------- | ----------------- | ----- |
| 1 | -1 | 4 |
| 1 | 9999 | 2 |
View on DB Fiddle
answered Nov 12 '18 at 13:03
Madhur BhaiyaMadhur Bhaiya
19.5k62236
add a comment |
Using CASE..WHEN expressions, you can determine a "modified" added_by value, and GROUP BY on the modified value instead.
Also, SUM(id) will not give you correct count of rows. It would rather add up the id values. You can use COUNT(*) instead to count the number of rows:
SELECT channel_id,
CASE
WHEN added_by <> 9999 THEN -1
ELSE 9999
END AS modified_added_by,
COUNT(*) as total
FROM your_table_name
WHERE channel_id = 1
GROUP BY channel_id, modified_added_by
Result
| channel_id | modified_added_by | total |
| ---------- | ----------------- | ----- |
| 1 | -1 | 4 |
| 1 | 9999 | 2 |
View on DB Fiddle
answered Nov 12 '18 at 13:03
Madhur BhaiyaMadhur Bhaiya
19.5k62236
Using CASE..WHEN expressions, you can determine a "modified" added_by value, and GROUP BY on the modified value instead.
Also, SUM(id) will not give you correct count of rows. It would rather add up the id values. You can use COUNT(*) instead to count the number of rows:
SELECT channel_id,
CASE
WHEN added_by <> 9999 THEN -1
ELSE 9999
END AS modified_added_by,
COUNT(*) as total
FROM your_table_name
WHERE channel_id = 1
GROUP BY channel_id, modified_added_by
Result
| channel_id | modified_added_by | total |
| ---------- | ----------------- | ----- |
| 1 | -1 | 4 |
| 1 | 9999 | 2 |
View on DB Fiddle
answered Nov 12 '18 at 13:03
Madhur BhaiyaMadhur Bhaiya
19.5k62236
edited Nov 12 '18 at 20:48
answered Nov 12 '18 at 13:03
Madhur BhaiyaMadhur Bhaiya
19.5k62236
answered Nov 12 '18 at 13:03
Madhur BhaiyaMadhur Bhaiya
19.5k62236
answered Nov 12 '18 at 13:03
Madhur BhaiyaMadhur Bhaiya
19.5k62236
19.5k62236
add a comment |
add a comment |
http://sqlfiddle.com/#!9/437406/2
SELECT channel_id,
IF(added_by=9999,9999,-1) new_col,
COUNT(*)
FROM `table`
GROUP BY channel_id, new_col
answered Nov 12 '18 at 14:14
AlexAlex
14.3k11836
add a comment |
http://sqlfiddle.com/#!9/437406/2
SELECT channel_id,
IF(added_by=9999,9999,-1) new_col,
COUNT(*)
FROM `table`
GROUP BY channel_id, new_col
answered Nov 12 '18 at 14:14
AlexAlex
14.3k11836
add a comment |
http://sqlfiddle.com/#!9/437406/2
SELECT channel_id,
IF(added_by=9999,9999,-1) new_col,
COUNT(*)
FROM `table`
GROUP BY channel_id, new_col
answered Nov 12 '18 at 14:14
AlexAlex
14.3k11836
http://sqlfiddle.com/#!9/437406/2
SELECT channel_id,
IF(added_by=9999,9999,-1) new_col,
COUNT(*)
FROM `table`
GROUP BY channel_id, new_col
answered Nov 12 '18 at 14:14
AlexAlex
14.3k11836
answered Nov 12 '18 at 14:14
AlexAlex
14.3k11836
answered Nov 12 '18 at 14:14
AlexAlex
14.3k11836
answered Nov 12 '18 at 14:14
AlexAlex
14.3k11836
14.3k11836
add a comment |
add a comment |
You can try this:
SELECT channel_id,
if(9999,9999,-1) as added_by,
sum(id) as total
from table
where channel_id = 1
group by channel_id,added_by;
edited Nov 12 '18 at 13:21
Francesco Boi
1,58421935
answered Nov 12 '18 at 13:08
user2485245user2485245
11
add a comment |
You can try this:
SELECT channel_id,
if(9999,9999,-1) as added_by,
sum(id) as total
from table
where channel_id = 1
group by channel_id,added_by;
edited Nov 12 '18 at 13:21
Francesco Boi
1,58421935
answered Nov 12 '18 at 13:08
user2485245user2485245
11
add a comment |
You can try this:
SELECT channel_id,
if(9999,9999,-1) as added_by,
sum(id) as total
from table
where channel_id = 1
group by channel_id,added_by;
edited Nov 12 '18 at 13:21
Francesco Boi
1,58421935
answered Nov 12 '18 at 13:08
user2485245user2485245
11
You can try this:
SELECT channel_id,
if(9999,9999,-1) as added_by,
sum(id) as total
from table
where channel_id = 1
group by channel_id,added_by;
edited Nov 12 '18 at 13:21
Francesco Boi
1,58421935
answered Nov 12 '18 at 13:08
user2485245user2485245
11
edited Nov 12 '18 at 13:21
Francesco Boi
1,58421935
edited Nov 12 '18 at 13:21
Francesco Boi
1,58421935
edited Nov 12 '18 at 13:21
Francesco Boi
1,58421935
1,58421935
answered Nov 12 '18 at 13:08
user2485245user2485245
11
answered Nov 12 '18 at 13:08
user2485245user2485245
11
answered Nov 12 '18 at 13:08
user2485245user2485245
11
11
add a comment |
add a comment |
Hope this will help you.
SELECT count(*) as total, channel_id, added_by from table group by channel_id, added_by;
answered Nov 12 '18 at 13:21
Dharmik RavalDharmik Raval
15
How it will generate -1 in added_by column.
– Brijesh Kumar Tripathi
Nov 12 '18 at 13:34
add a comment |
Hope this will help you.
SELECT count(*) as total, channel_id, added_by from table group by channel_id, added_by;
answered Nov 12 '18 at 13:21
Dharmik RavalDharmik Raval
15
How it will generate -1 in added_by column.
– Brijesh Kumar Tripathi
Nov 12 '18 at 13:34
add a comment |
Hope this will help you.
SELECT count(*) as total, channel_id, added_by from table group by channel_id, added_by;
answered Nov 12 '18 at 13:21
Dharmik RavalDharmik Raval
15
Hope this will help you.
SELECT count(*) as total, channel_id, added_by from table group by channel_id, added_by;
answered Nov 12 '18 at 13:21
Dharmik RavalDharmik Raval
15
answered Nov 12 '18 at 13:21
Dharmik RavalDharmik Raval
15
answered Nov 12 '18 at 13:21
Dharmik RavalDharmik Raval
15
answered Nov 12 '18 at 13:21
Dharmik RavalDharmik Raval
15
15
How it will generate -1 in added_by column.
– Brijesh Kumar Tripathi
Nov 12 '18 at 13:34
add a comment |
How it will generate -1 in added_by column.
– Brijesh Kumar Tripathi
Nov 12 '18 at 13:34
How it will generate -1 in added_by column.
– Brijesh Kumar Tripathi
Nov 12 '18 at 13:34
How it will generate -1 in added_by column.
– Brijesh Kumar Tripathi
Nov 12 '18 at 13:34
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%2f53262733%2fgroup-by-with-conditions%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
