Aggregate and find most used room by customer using SQL
0
I am trying to find all bookings grouped by my customers, and only showing the most used room by them (and the count booked).
This is the query I have so far, but the problem is that it will show all their bookings and counts, I'm only interested in their most booked room, I'm not sure what the best function to use to achieve this. I thought querying by the account table and then a subquery for getting the booking numbers but I feel this might be less efficient (especially as we have around 10M account records).
Schema:
CREATE TABLE rooms (
room_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
room_name varchar(50)
);
CREATE TABLE bookings (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
account_id int,
room_id int
);
Sample data:
INSERT INTO rooms (room_id, room_name)
VALUES ('1', 'Suite A'),
('2', 'Suite B'),
('3', 'Suite C'),
('4', 'Suite D'),
('5', 'Suite X');
INSERT INTO bookings (account_id, room_id)
VALUES ('123', '1'),
('123', '1'),
('123', '1'),
('123', '1'),
('123', '1'),
('123', '2'),
('123', '3'),
('123', '4'),
('123', '4'),
('123', '5'),
('123', '1'),
('124', '4'),
('124', '5'),
('124', '1');
Query:
select account_id, count(room_id), room_id
from bookings
group by account_id, room_id
SQL Fiddle Link
Desired Output:
account_id | most booked room | count
123 | Room A | 2
124 | Room B | 30
sql tsql
edited Nov 13 '18 at 12:10
Zohar Peled
52.7k73273
asked Nov 13 '18 at 11:57
ImranImran
4,12574679
add a comment |
0
I am trying to find all bookings grouped by my customers, and only showing the most used room by them (and the count booked).
This is the query I have so far, but the problem is that it will show all their bookings and counts, I'm only interested in their most booked room, I'm not sure what the best function to use to achieve this. I thought querying by the account table and then a subquery for getting the booking numbers but I feel this might be less efficient (especially as we have around 10M account records).
Schema:
CREATE TABLE rooms (
room_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
room_name varchar(50)
);
CREATE TABLE bookings (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
account_id int,
room_id int
);
Sample data:
INSERT INTO rooms (room_id, room_name)
VALUES ('1', 'Suite A'),
('2', 'Suite B'),
('3', 'Suite C'),
('4', 'Suite D'),
('5', 'Suite X');
INSERT INTO bookings (account_id, room_id)
VALUES ('123', '1'),
('123', '1'),
('123', '1'),
('123', '1'),
('123', '1'),
('123', '2'),
('123', '3'),
('123', '4'),
('123', '4'),
('123', '5'),
('123', '1'),
('124', '4'),
('124', '5'),
('124', '1');
Query:
select account_id, count(room_id), room_id
from bookings
group by account_id, room_id
SQL Fiddle Link
Desired Output:
account_id | most booked room | count
123 | Room A | 2
124 | Room B | 30
sql tsql
edited Nov 13 '18 at 12:10
Zohar Peled
52.7k73273
asked Nov 13 '18 at 11:57
ImranImran
4,12574679
1
Sample data and desired results in the question would clarify what you want. I'm not sure why you set up a MySQL fiddle for a question tagged tsql.
– Gordon Linoff
Nov 13 '18 at 11:58
I have updated the question to include the desired output, tsql wasn't supported in the tool used but the question is for tsql not mysql hence the tags used.
– Imran
Nov 13 '18 at 12:07
add a comment |
0
0
0
I am trying to find all bookings grouped by my customers, and only showing the most used room by them (and the count booked).
This is the query I have so far, but the problem is that it will show all their bookings and counts, I'm only interested in their most booked room, I'm not sure what the best function to use to achieve this. I thought querying by the account table and then a subquery for getting the booking numbers but I feel this might be less efficient (especially as we have around 10M account records).
Schema:
CREATE TABLE rooms (
room_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
room_name varchar(50)
);
CREATE TABLE bookings (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
account_id int,
room_id int
);
Sample data:
INSERT INTO rooms (room_id, room_name)
VALUES ('1', 'Suite A'),
('2', 'Suite B'),
('3', 'Suite C'),
('4', 'Suite D'),
('5', 'Suite X');
INSERT INTO bookings (account_id, room_id)
VALUES ('123', '1'),
('123', '1'),
('123', '1'),
('123', '1'),
('123', '1'),
('123', '2'),
('123', '3'),
('123', '4'),
('123', '4'),
('123', '5'),
('123', '1'),
('124', '4'),
('124', '5'),
('124', '1');
Query:
select account_id, count(room_id), room_id
from bookings
group by account_id, room_id
SQL Fiddle Link
Desired Output:
account_id | most booked room | count
123 | Room A | 2
124 | Room B | 30
sql tsql
edited Nov 13 '18 at 12:10
Zohar Peled
52.7k73273
asked Nov 13 '18 at 11:57
ImranImran
4,12574679
I am trying to find all bookings grouped by my customers, and only showing the most used room by them (and the count booked).
This is the query I have so far, but the problem is that it will show all their bookings and counts, I'm only interested in their most booked room, I'm not sure what the best function to use to achieve this. I thought querying by the account table and then a subquery for getting the booking numbers but I feel this might be less efficient (especially as we have around 10M account records).
Schema:
CREATE TABLE rooms (
room_id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
room_name varchar(50)
);
CREATE TABLE bookings (
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
account_id int,
room_id int
);
Sample data:
INSERT INTO rooms (room_id, room_name)
VALUES ('1', 'Suite A'),
('2', 'Suite B'),
('3', 'Suite C'),
('4', 'Suite D'),
('5', 'Suite X');
INSERT INTO bookings (account_id, room_id)
VALUES ('123', '1'),
('123', '1'),
('123', '1'),
('123', '1'),
('123', '1'),
('123', '2'),
('123', '3'),
('123', '4'),
('123', '4'),
('123', '5'),
('123', '1'),
('124', '4'),
('124', '5'),
('124', '1');
Query:
select account_id, count(room_id), room_id
from bookings
group by account_id, room_id
SQL Fiddle Link
Desired Output:
account_id | most booked room | count
123 | Room A | 2
124 | Room B | 30
sql tsql
sql tsql
edited Nov 13 '18 at 12:10
Zohar Peled
52.7k73273
asked Nov 13 '18 at 11:57
ImranImran
4,12574679
edited Nov 13 '18 at 12:10
Zohar Peled
52.7k73273
asked Nov 13 '18 at 11:57
ImranImran
4,12574679
edited Nov 13 '18 at 12:10
Zohar Peled
52.7k73273
edited Nov 13 '18 at 12:10
Zohar Peled
52.7k73273
edited Nov 13 '18 at 12:10
Zohar Peled
52.7k73273
52.7k73273
asked Nov 13 '18 at 11:57
ImranImran
4,12574679
asked Nov 13 '18 at 11:57
ImranImran
4,12574679
asked Nov 13 '18 at 11:57
ImranImran
4,12574679
4,12574679
1
Sample data and desired results in the question would clarify what you want. I'm not sure why you set up a MySQL fiddle for a question tagged tsql.
– Gordon Linoff
Nov 13 '18 at 11:58
I have updated the question to include the desired output, tsql wasn't supported in the tool used but the question is for tsql not mysql hence the tags used.
– Imran
Nov 13 '18 at 12:07
add a comment |
1
Sample data and desired results in the question would clarify what you want. I'm not sure why you set up a MySQL fiddle for a question tagged tsql.
– Gordon Linoff
Nov 13 '18 at 11:58
I have updated the question to include the desired output, tsql wasn't supported in the tool used but the question is for tsql not mysql hence the tags used.
– Imran
Nov 13 '18 at 12:07
1
1
Sample data and desired results in the question would clarify what you want. I'm not sure why you set up a MySQL fiddle for a question tagged tsql.
– Gordon Linoff
Nov 13 '18 at 11:58
Sample data and desired results in the question would clarify what you want. I'm not sure why you set up a MySQL fiddle for a question tagged tsql.
– Gordon Linoff
Nov 13 '18 at 11:58
I have updated the question to include the desired output, tsql wasn't supported in the tool used but the question is for tsql not mysql hence the tags used.
– Imran
Nov 13 '18 at 12:07
I have updated the question to include the desired output, tsql wasn't supported in the tool used but the question is for tsql not mysql hence the tags used.
– Imran
Nov 13 '18 at 12:07
add a comment |
2 Answers
2
active
oldest
votes
0
You want the most common room per account. Statistically, this is called the mode.
You can calculate it using window functions:
select ar.*
from (select account_id, room_id, count(*) as cnt,
row_number() over (partition by account_id order by count(*) desc) as seqnum
from bookings
group by account_id, room_id
) ar
where seqnum = 1;
Here is the dbfiddle, using MySQL 8, so the syntax is more compatible with SQL Server.
answered Nov 13 '18 at 12:00
Gordon LinoffGordon Linoff
762k35296400
In the event where there is an account with equal number of bookings in a venue... what is the output? and is it possible to say order by lastbookings.order_date
– Imran
Nov 13 '18 at 17:04
Can you also explain what thepartitionandoverfunctions are doing here, I'd like to understand how this works. Thanks!
– Imran
Nov 13 '18 at 17:08
@Imran . . . docs.microsoft.com/en-us/sql/t-sql/functions/…. If there are equal numbers, then an arbitrary matching value is returned. For the most recent room, you can useorder by count(*) desc, max(order_date) desc.
– Gordon Linoff
Nov 13 '18 at 18:39
add a comment |
0
You can use correlated subquery
DEMO
select * from
(select account_id, count(room_id) as cnt, room_id
from bookings
group by account_id, room_id
)A inner join rooms on A.room_id=rooms.room_id
where cnt in (select max(cnt) from
(select account_id, count(room_id) as cnt, room_id
from bookings
group by account_id, room_id
)B where A.account_id=B.account_id)
answered Nov 13 '18 at 12:07
fa06fa06
11.7k2917
In the demo provided this still shows more than one instance of eachaccount_id
– Imran
Nov 13 '18 at 12:14
it's because of your sample data - you can see that for 124 you've entered 1, 4, 5 room - so the room no is unique here, so for each room the count is one - in this case which one you want to pick
– fa06
Nov 13 '18 at 12:15
@fa06 whenever I tries to access the demo it redirects to the homepage of dbfiddle
– Sanal Sunny
Nov 13 '18 at 12:17
@SanalSunny - db-fiddle.com/f/hNedhkY8AHSYcVEVmrSV8q/0 - you need to exeute the query
– fa06
Nov 13 '18 at 12:18
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%2f53280551%2faggregate-and-find-most-used-room-by-customer-using-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
0
You want the most common room per account. Statistically, this is called the mode.
You can calculate it using window functions:
select ar.*
from (select account_id, room_id, count(*) as cnt,
row_number() over (partition by account_id order by count(*) desc) as seqnum
from bookings
group by account_id, room_id
) ar
where seqnum = 1;
Here is the dbfiddle, using MySQL 8, so the syntax is more compatible with SQL Server.
answered Nov 13 '18 at 12:00
Gordon LinoffGordon Linoff
762k35296400
In the event where there is an account with equal number of bookings in a venue... what is the output? and is it possible to say order by lastbookings.order_date
– Imran
Nov 13 '18 at 17:04
Can you also explain what thepartitionandoverfunctions are doing here, I'd like to understand how this works. Thanks!
– Imran
Nov 13 '18 at 17:08
@Imran . . . docs.microsoft.com/en-us/sql/t-sql/functions/…. If there are equal numbers, then an arbitrary matching value is returned. For the most recent room, you can useorder by count(*) desc, max(order_date) desc.
– Gordon Linoff
Nov 13 '18 at 18:39
add a comment |
0
You want the most common room per account. Statistically, this is called the mode.
You can calculate it using window functions:
select ar.*
from (select account_id, room_id, count(*) as cnt,
row_number() over (partition by account_id order by count(*) desc) as seqnum
from bookings
group by account_id, room_id
) ar
where seqnum = 1;
Here is the dbfiddle, using MySQL 8, so the syntax is more compatible with SQL Server.
answered Nov 13 '18 at 12:00
Gordon LinoffGordon Linoff
762k35296400
In the event where there is an account with equal number of bookings in a venue... what is the output? and is it possible to say order by lastbookings.order_date
– Imran
Nov 13 '18 at 17:04
Can you also explain what thepartitionandoverfunctions are doing here, I'd like to understand how this works. Thanks!
– Imran
Nov 13 '18 at 17:08
@Imran . . . docs.microsoft.com/en-us/sql/t-sql/functions/…. If there are equal numbers, then an arbitrary matching value is returned. For the most recent room, you can useorder by count(*) desc, max(order_date) desc.
– Gordon Linoff
Nov 13 '18 at 18:39
add a comment |
0
0
0
You want the most common room per account. Statistically, this is called the mode.
You can calculate it using window functions:
select ar.*
from (select account_id, room_id, count(*) as cnt,
row_number() over (partition by account_id order by count(*) desc) as seqnum
from bookings
group by account_id, room_id
) ar
where seqnum = 1;
Here is the dbfiddle, using MySQL 8, so the syntax is more compatible with SQL Server.
answered Nov 13 '18 at 12:00
Gordon LinoffGordon Linoff
762k35296400
You want the most common room per account. Statistically, this is called the mode.
You can calculate it using window functions:
select ar.*
from (select account_id, room_id, count(*) as cnt,
row_number() over (partition by account_id order by count(*) desc) as seqnum
from bookings
group by account_id, room_id
) ar
where seqnum = 1;
Here is the dbfiddle, using MySQL 8, so the syntax is more compatible with SQL Server.
answered Nov 13 '18 at 12:00
Gordon LinoffGordon Linoff
762k35296400
edited Nov 13 '18 at 12:46
answered Nov 13 '18 at 12:00
Gordon LinoffGordon Linoff
762k35296400
answered Nov 13 '18 at 12:00
Gordon LinoffGordon Linoff
762k35296400
answered Nov 13 '18 at 12:00
Gordon LinoffGordon Linoff
762k35296400
762k35296400
In the event where there is an account with equal number of bookings in a venue... what is the output? and is it possible to say order by lastbookings.order_date
– Imran
Nov 13 '18 at 17:04
Can you also explain what thepartitionandoverfunctions are doing here, I'd like to understand how this works. Thanks!
– Imran
Nov 13 '18 at 17:08
@Imran . . . docs.microsoft.com/en-us/sql/t-sql/functions/…. If there are equal numbers, then an arbitrary matching value is returned. For the most recent room, you can useorder by count(*) desc, max(order_date) desc.
– Gordon Linoff
Nov 13 '18 at 18:39
add a comment |
In the event where there is an account with equal number of bookings in a venue... what is the output? and is it possible to say order by lastbookings.order_date
– Imran
Nov 13 '18 at 17:04
Can you also explain what thepartitionandoverfunctions are doing here, I'd like to understand how this works. Thanks!
– Imran
Nov 13 '18 at 17:08
@Imran . . . docs.microsoft.com/en-us/sql/t-sql/functions/…. If there are equal numbers, then an arbitrary matching value is returned. For the most recent room, you can useorder by count(*) desc, max(order_date) desc.
– Gordon Linoff
Nov 13 '18 at 18:39
In the event where there is an account with equal number of bookings in a venue... what is the output? and is it possible to say order by last
bookings.order_date– Imran
Nov 13 '18 at 17:04
In the event where there is an account with equal number of bookings in a venue... what is the output? and is it possible to say order by last
bookings.order_date– Imran
Nov 13 '18 at 17:04
Can you also explain what the
partition and over functions are doing here, I'd like to understand how this works. Thanks!– Imran
Nov 13 '18 at 17:08
Can you also explain what the
partition and over functions are doing here, I'd like to understand how this works. Thanks!– Imran
Nov 13 '18 at 17:08
@Imran . . . docs.microsoft.com/en-us/sql/t-sql/functions/…. If there are equal numbers, then an arbitrary matching value is returned. For the most recent room, you can use
order by count(*) desc, max(order_date) desc.– Gordon Linoff
Nov 13 '18 at 18:39
@Imran . . . docs.microsoft.com/en-us/sql/t-sql/functions/…. If there are equal numbers, then an arbitrary matching value is returned. For the most recent room, you can use
order by count(*) desc, max(order_date) desc.– Gordon Linoff
Nov 13 '18 at 18:39
add a comment |
0
You can use correlated subquery
DEMO
select * from
(select account_id, count(room_id) as cnt, room_id
from bookings
group by account_id, room_id
)A inner join rooms on A.room_id=rooms.room_id
where cnt in (select max(cnt) from
(select account_id, count(room_id) as cnt, room_id
from bookings
group by account_id, room_id
)B where A.account_id=B.account_id)
answered Nov 13 '18 at 12:07
fa06fa06
11.7k2917
In the demo provided this still shows more than one instance of eachaccount_id
– Imran
Nov 13 '18 at 12:14
it's because of your sample data - you can see that for 124 you've entered 1, 4, 5 room - so the room no is unique here, so for each room the count is one - in this case which one you want to pick
– fa06
Nov 13 '18 at 12:15
@fa06 whenever I tries to access the demo it redirects to the homepage of dbfiddle
– Sanal Sunny
Nov 13 '18 at 12:17
@SanalSunny - db-fiddle.com/f/hNedhkY8AHSYcVEVmrSV8q/0 - you need to exeute the query
– fa06
Nov 13 '18 at 12:18
add a comment |
0
You can use correlated subquery
DEMO
select * from
(select account_id, count(room_id) as cnt, room_id
from bookings
group by account_id, room_id
)A inner join rooms on A.room_id=rooms.room_id
where cnt in (select max(cnt) from
(select account_id, count(room_id) as cnt, room_id
from bookings
group by account_id, room_id
)B where A.account_id=B.account_id)
answered Nov 13 '18 at 12:07
fa06fa06
11.7k2917
In the demo provided this still shows more than one instance of eachaccount_id
– Imran
Nov 13 '18 at 12:14
it's because of your sample data - you can see that for 124 you've entered 1, 4, 5 room - so the room no is unique here, so for each room the count is one - in this case which one you want to pick
– fa06
Nov 13 '18 at 12:15
@fa06 whenever I tries to access the demo it redirects to the homepage of dbfiddle
– Sanal Sunny
Nov 13 '18 at 12:17
@SanalSunny - db-fiddle.com/f/hNedhkY8AHSYcVEVmrSV8q/0 - you need to exeute the query
– fa06
Nov 13 '18 at 12:18
add a comment |
0
0
0
You can use correlated subquery
DEMO
select * from
(select account_id, count(room_id) as cnt, room_id
from bookings
group by account_id, room_id
)A inner join rooms on A.room_id=rooms.room_id
where cnt in (select max(cnt) from
(select account_id, count(room_id) as cnt, room_id
from bookings
group by account_id, room_id
)B where A.account_id=B.account_id)
answered Nov 13 '18 at 12:07
fa06fa06
11.7k2917
You can use correlated subquery
DEMO
select * from
(select account_id, count(room_id) as cnt, room_id
from bookings
group by account_id, room_id
)A inner join rooms on A.room_id=rooms.room_id
where cnt in (select max(cnt) from
(select account_id, count(room_id) as cnt, room_id
from bookings
group by account_id, room_id
)B where A.account_id=B.account_id)
answered Nov 13 '18 at 12:07
fa06fa06
11.7k2917
edited Nov 13 '18 at 12:15
answered Nov 13 '18 at 12:07
fa06fa06
11.7k2917
answered Nov 13 '18 at 12:07
fa06fa06
11.7k2917
answered Nov 13 '18 at 12:07
fa06fa06
11.7k2917
11.7k2917
In the demo provided this still shows more than one instance of eachaccount_id
– Imran
Nov 13 '18 at 12:14
it's because of your sample data - you can see that for 124 you've entered 1, 4, 5 room - so the room no is unique here, so for each room the count is one - in this case which one you want to pick
– fa06
Nov 13 '18 at 12:15
@fa06 whenever I tries to access the demo it redirects to the homepage of dbfiddle
– Sanal Sunny
Nov 13 '18 at 12:17
@SanalSunny - db-fiddle.com/f/hNedhkY8AHSYcVEVmrSV8q/0 - you need to exeute the query
– fa06
Nov 13 '18 at 12:18
add a comment |
In the demo provided this still shows more than one instance of eachaccount_id
– Imran
Nov 13 '18 at 12:14
it's because of your sample data - you can see that for 124 you've entered 1, 4, 5 room - so the room no is unique here, so for each room the count is one - in this case which one you want to pick
– fa06
Nov 13 '18 at 12:15
@fa06 whenever I tries to access the demo it redirects to the homepage of dbfiddle
– Sanal Sunny
Nov 13 '18 at 12:17
@SanalSunny - db-fiddle.com/f/hNedhkY8AHSYcVEVmrSV8q/0 - you need to exeute the query
– fa06
Nov 13 '18 at 12:18
In the demo provided this still shows more than one instance of each
account_id– Imran
Nov 13 '18 at 12:14
In the demo provided this still shows more than one instance of each
account_id– Imran
Nov 13 '18 at 12:14
it's because of your sample data - you can see that for 124 you've entered 1, 4, 5 room - so the room no is unique here, so for each room the count is one - in this case which one you want to pick
– fa06
Nov 13 '18 at 12:15
it's because of your sample data - you can see that for 124 you've entered 1, 4, 5 room - so the room no is unique here, so for each room the count is one - in this case which one you want to pick
– fa06
Nov 13 '18 at 12:15
@fa06 whenever I tries to access the demo it redirects to the homepage of dbfiddle
– Sanal Sunny
Nov 13 '18 at 12:17
@fa06 whenever I tries to access the demo it redirects to the homepage of dbfiddle
– Sanal Sunny
Nov 13 '18 at 12:17
@SanalSunny - db-fiddle.com/f/hNedhkY8AHSYcVEVmrSV8q/0 - you need to exeute the query
– fa06
Nov 13 '18 at 12:18
@SanalSunny - db-fiddle.com/f/hNedhkY8AHSYcVEVmrSV8q/0 - you need to exeute the query
– fa06
Nov 13 '18 at 12:18
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%2f53280551%2faggregate-and-find-most-used-room-by-customer-using-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
1
Sample data and desired results in the question would clarify what you want. I'm not sure why you set up a MySQL fiddle for a question tagged tsql.
– Gordon Linoff
Nov 13 '18 at 11:58
I have updated the question to include the desired output, tsql wasn't supported in the tool used but the question is for tsql not mysql hence the tags used.
– Imran
Nov 13 '18 at 12:07