getting distinct result with few more conditions in mysql [duplicate]
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
0
This question already has an answer here:
MySQL pivot table
8 answers
Hai i have some sample data
bookId | bookPnr | bookDate | bookFullName | bookMobile | bookEmail | bookSource
9876543210 | BPT1100000000 | 2018-11-18 | User 1 | 9876543210 | test@gmail.com | Redbus
9876543211 | BPT1100000001 | 2017-11-18 | User 2 | 9876543211 | testOne@gmail.com | Redbus
9876543212 | BPT1100000002 | 2017-11-18 | User 3 | 9876543214 | testtwo@gmail.com | TicketGoose
I need a result like
Mobile | 2018 | 2017 | 2016 | Redbus | TicketGoose | total
9876543210 | 2 | 3 | 6 | 2 | 2 | 11
9876543211 | 1 | 1 | 1 | 2 | 1 | 3
So i need distinct mobile numbers based on year and source
I did query something like,
SELECT count(bookId), bookMobile, bookDate, bookSource FROM `booking_info`
GROUP by bookMobile, MONTH(bookDate), bookSource ORDER BY bookMobile DESC
Is it possible to do it with single query or we have to use PHP Any suggetions will be really appreciated.
php mysql
edited Nov 17 '18 at 7:37
Strawberry
26.8k83250
asked Nov 17 '18 at 7:23
Pattatharasu NatarajPattatharasu Nataraj
733420
marked as duplicate by Chowkidar Madhur Bhaiya, Strawberry mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 17 '18 at 7:38
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
|
show 5 more comments
0
This question already has an answer here:
MySQL pivot table
8 answers
Hai i have some sample data
bookId | bookPnr | bookDate | bookFullName | bookMobile | bookEmail | bookSource
9876543210 | BPT1100000000 | 2018-11-18 | User 1 | 9876543210 | test@gmail.com | Redbus
9876543211 | BPT1100000001 | 2017-11-18 | User 2 | 9876543211 | testOne@gmail.com | Redbus
9876543212 | BPT1100000002 | 2017-11-18 | User 3 | 9876543214 | testtwo@gmail.com | TicketGoose
I need a result like
Mobile | 2018 | 2017 | 2016 | Redbus | TicketGoose | total
9876543210 | 2 | 3 | 6 | 2 | 2 | 11
9876543211 | 1 | 1 | 1 | 2 | 1 | 3
So i need distinct mobile numbers based on year and source
I did query something like,
SELECT count(bookId), bookMobile, bookDate, bookSource FROM `booking_info`
GROUP by bookMobile, MONTH(bookDate), bookSource ORDER BY bookMobile DESC
Is it possible to do it with single query or we have to use PHP Any suggetions will be really appreciated.
php mysql
edited Nov 17 '18 at 7:37
Strawberry
26.8k83250
asked Nov 17 '18 at 7:23
Pattatharasu NatarajPattatharasu Nataraj
733420
marked as duplicate by Chowkidar Madhur Bhaiya, Strawberry mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 17 '18 at 7:38
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
MySQL <> SQL server. Please specify which RDBMS you are using.
– Chowkidar Madhur Bhaiya
Nov 17 '18 at 7:25
i am using mysql
– Pattatharasu Nataraj
Nov 17 '18 at 7:25
2
add a proper data sample .. coherent with your expected result
– scaisEdge
Nov 17 '18 at 7:25
data sample updated
– Pattatharasu Nataraj
Nov 17 '18 at 7:30
How many distinct BookSources are there? single digit? tens? more than tens? or do you just wantRedbus | TicketGoose?
– Used_By_Already
Nov 17 '18 at 7:32
|
show 5 more comments
0
0
0
This question already has an answer here:
MySQL pivot table
8 answers
Hai i have some sample data
bookId | bookPnr | bookDate | bookFullName | bookMobile | bookEmail | bookSource
9876543210 | BPT1100000000 | 2018-11-18 | User 1 | 9876543210 | test@gmail.com | Redbus
9876543211 | BPT1100000001 | 2017-11-18 | User 2 | 9876543211 | testOne@gmail.com | Redbus
9876543212 | BPT1100000002 | 2017-11-18 | User 3 | 9876543214 | testtwo@gmail.com | TicketGoose
I need a result like
Mobile | 2018 | 2017 | 2016 | Redbus | TicketGoose | total
9876543210 | 2 | 3 | 6 | 2 | 2 | 11
9876543211 | 1 | 1 | 1 | 2 | 1 | 3
So i need distinct mobile numbers based on year and source
I did query something like,
SELECT count(bookId), bookMobile, bookDate, bookSource FROM `booking_info`
GROUP by bookMobile, MONTH(bookDate), bookSource ORDER BY bookMobile DESC
Is it possible to do it with single query or we have to use PHP Any suggetions will be really appreciated.
php mysql
edited Nov 17 '18 at 7:37
Strawberry
26.8k83250
asked Nov 17 '18 at 7:23
Pattatharasu NatarajPattatharasu Nataraj
733420
This question already has an answer here:
MySQL pivot table
8 answers
Hai i have some sample data
bookId | bookPnr | bookDate | bookFullName | bookMobile | bookEmail | bookSource
9876543210 | BPT1100000000 | 2018-11-18 | User 1 | 9876543210 | test@gmail.com | Redbus
9876543211 | BPT1100000001 | 2017-11-18 | User 2 | 9876543211 | testOne@gmail.com | Redbus
9876543212 | BPT1100000002 | 2017-11-18 | User 3 | 9876543214 | testtwo@gmail.com | TicketGoose
I need a result like
Mobile | 2018 | 2017 | 2016 | Redbus | TicketGoose | total
9876543210 | 2 | 3 | 6 | 2 | 2 | 11
9876543211 | 1 | 1 | 1 | 2 | 1 | 3
So i need distinct mobile numbers based on year and source
I did query something like,
SELECT count(bookId), bookMobile, bookDate, bookSource FROM `booking_info`
GROUP by bookMobile, MONTH(bookDate), bookSource ORDER BY bookMobile DESC
Is it possible to do it with single query or we have to use PHP Any suggetions will be really appreciated.
This question already has an answer here:
MySQL pivot table
8 answers
php mysql
php mysql
edited Nov 17 '18 at 7:37
Strawberry
26.8k83250
asked Nov 17 '18 at 7:23
Pattatharasu NatarajPattatharasu Nataraj
733420
edited Nov 17 '18 at 7:37
Strawberry
26.8k83250
asked Nov 17 '18 at 7:23
Pattatharasu NatarajPattatharasu Nataraj
733420
edited Nov 17 '18 at 7:37
Strawberry
26.8k83250
edited Nov 17 '18 at 7:37
Strawberry
26.8k83250
edited Nov 17 '18 at 7:37
Strawberry
26.8k83250
26.8k83250
asked Nov 17 '18 at 7:23
Pattatharasu NatarajPattatharasu Nataraj
733420
asked Nov 17 '18 at 7:23
Pattatharasu NatarajPattatharasu Nataraj
733420
asked Nov 17 '18 at 7:23
Pattatharasu NatarajPattatharasu Nataraj
733420
733420
marked as duplicate by Chowkidar Madhur Bhaiya, Strawberry mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 17 '18 at 7:38
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Chowkidar Madhur Bhaiya, Strawberry mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 17 '18 at 7:38
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
MySQL <> SQL server. Please specify which RDBMS you are using.
– Chowkidar Madhur Bhaiya
Nov 17 '18 at 7:25
i am using mysql
– Pattatharasu Nataraj
Nov 17 '18 at 7:25
2
add a proper data sample .. coherent with your expected result
– scaisEdge
Nov 17 '18 at 7:25
data sample updated
– Pattatharasu Nataraj
Nov 17 '18 at 7:30
How many distinct BookSources are there? single digit? tens? more than tens? or do you just wantRedbus | TicketGoose?
– Used_By_Already
Nov 17 '18 at 7:32
|
show 5 more comments
MySQL <> SQL server. Please specify which RDBMS you are using.
– Chowkidar Madhur Bhaiya
Nov 17 '18 at 7:25
i am using mysql
– Pattatharasu Nataraj
Nov 17 '18 at 7:25
2
add a proper data sample .. coherent with your expected result
– scaisEdge
Nov 17 '18 at 7:25
data sample updated
– Pattatharasu Nataraj
Nov 17 '18 at 7:30
How many distinct BookSources are there? single digit? tens? more than tens? or do you just wantRedbus | TicketGoose?
– Used_By_Already
Nov 17 '18 at 7:32
MySQL <> SQL server. Please specify which RDBMS you are using.
– Chowkidar Madhur Bhaiya
Nov 17 '18 at 7:25
MySQL <> SQL server. Please specify which RDBMS you are using.
– Chowkidar Madhur Bhaiya
Nov 17 '18 at 7:25
i am using mysql
– Pattatharasu Nataraj
Nov 17 '18 at 7:25
i am using mysql
– Pattatharasu Nataraj
Nov 17 '18 at 7:25
2
2
add a proper data sample .. coherent with your expected result
– scaisEdge
Nov 17 '18 at 7:25
add a proper data sample .. coherent with your expected result
– scaisEdge
Nov 17 '18 at 7:25
data sample updated
– Pattatharasu Nataraj
Nov 17 '18 at 7:30
data sample updated
– Pattatharasu Nataraj
Nov 17 '18 at 7:30
How many distinct BookSources are there? single digit? tens? more than tens? or do you just want
Redbus | TicketGoose ?– Used_By_Already
Nov 17 '18 at 7:32
How many distinct BookSources are there? single digit? tens? more than tens? or do you just want
Redbus | TicketGoose ?– Used_By_Already
Nov 17 '18 at 7:32
|
show 5 more comments
1 Answer
1
active
oldest
votes
1
You can use "conditional aggregates" to "pivot" your data. Basically this means placing a case expression inside an aggregation function. Here I have used COUNT():
SELECT
bookMobile
, count(case when year(bookDate) = 2016 then 1 end) as `2016`
, count(case when year(bookDate) = 2017 then 1 end) as `2017`
, count(case when year(bookDate) = 2018 then 1 end) as `2018`
, count(case when bookSource = 'Redbus' then 1 end) as Redbus
, count(case when bookSource = 'TicketGoose' then 1 end) as TicketGoose
FROM booking_info
GROUP BY
bookMobile
ORDER BY
bookMobile DESC
answered Nov 17 '18 at 7:37
Used_By_AlreadyUsed_By_Already
23.2k22139
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
You can use "conditional aggregates" to "pivot" your data. Basically this means placing a case expression inside an aggregation function. Here I have used COUNT():
SELECT
bookMobile
, count(case when year(bookDate) = 2016 then 1 end) as `2016`
, count(case when year(bookDate) = 2017 then 1 end) as `2017`
, count(case when year(bookDate) = 2018 then 1 end) as `2018`
, count(case when bookSource = 'Redbus' then 1 end) as Redbus
, count(case when bookSource = 'TicketGoose' then 1 end) as TicketGoose
FROM booking_info
GROUP BY
bookMobile
ORDER BY
bookMobile DESC
answered Nov 17 '18 at 7:37
Used_By_AlreadyUsed_By_Already
23.2k22139
add a comment |
1
You can use "conditional aggregates" to "pivot" your data. Basically this means placing a case expression inside an aggregation function. Here I have used COUNT():
SELECT
bookMobile
, count(case when year(bookDate) = 2016 then 1 end) as `2016`
, count(case when year(bookDate) = 2017 then 1 end) as `2017`
, count(case when year(bookDate) = 2018 then 1 end) as `2018`
, count(case when bookSource = 'Redbus' then 1 end) as Redbus
, count(case when bookSource = 'TicketGoose' then 1 end) as TicketGoose
FROM booking_info
GROUP BY
bookMobile
ORDER BY
bookMobile DESC
answered Nov 17 '18 at 7:37
Used_By_AlreadyUsed_By_Already
23.2k22139
add a comment |
1
1
1
You can use "conditional aggregates" to "pivot" your data. Basically this means placing a case expression inside an aggregation function. Here I have used COUNT():
SELECT
bookMobile
, count(case when year(bookDate) = 2016 then 1 end) as `2016`
, count(case when year(bookDate) = 2017 then 1 end) as `2017`
, count(case when year(bookDate) = 2018 then 1 end) as `2018`
, count(case when bookSource = 'Redbus' then 1 end) as Redbus
, count(case when bookSource = 'TicketGoose' then 1 end) as TicketGoose
FROM booking_info
GROUP BY
bookMobile
ORDER BY
bookMobile DESC
answered Nov 17 '18 at 7:37
Used_By_AlreadyUsed_By_Already
23.2k22139
You can use "conditional aggregates" to "pivot" your data. Basically this means placing a case expression inside an aggregation function. Here I have used COUNT():
SELECT
bookMobile
, count(case when year(bookDate) = 2016 then 1 end) as `2016`
, count(case when year(bookDate) = 2017 then 1 end) as `2017`
, count(case when year(bookDate) = 2018 then 1 end) as `2018`
, count(case when bookSource = 'Redbus' then 1 end) as Redbus
, count(case when bookSource = 'TicketGoose' then 1 end) as TicketGoose
FROM booking_info
GROUP BY
bookMobile
ORDER BY
bookMobile DESC
answered Nov 17 '18 at 7:37
Used_By_AlreadyUsed_By_Already
23.2k22139
answered Nov 17 '18 at 7:37
Used_By_AlreadyUsed_By_Already
23.2k22139
answered Nov 17 '18 at 7:37
Used_By_AlreadyUsed_By_Already
23.2k22139
answered Nov 17 '18 at 7:37
Used_By_AlreadyUsed_By_Already
23.2k22139
23.2k22139
add a comment |
add a comment |
MySQL <> SQL server. Please specify which RDBMS you are using.
– Chowkidar Madhur Bhaiya
Nov 17 '18 at 7:25
i am using mysql
– Pattatharasu Nataraj
Nov 17 '18 at 7:25
2
add a proper data sample .. coherent with your expected result
– scaisEdge
Nov 17 '18 at 7:25
data sample updated
– Pattatharasu Nataraj
Nov 17 '18 at 7:30
How many distinct BookSources are there? single digit? tens? more than tens? or do you just want
Redbus | TicketGoose?– Used_By_Already
Nov 17 '18 at 7:32