Find count group by id in SQL Server
0
I need some help to solve this query. I have a table which contains the ages of the passengers who are going to stay in a room which is mentioned below:
Age RoomId
----- ---
1 1
12 1
8 1
19 1
3 2
12 2
18 2
21 3
Also, I have properties table which contains the maximum age of the child and maximum age of the infant. Based on the age of the passenger, I need to segregate them to adult, child, and infant to each of the properties.
Properties table structure
Property Id Maximum_child_age Maximum_infant_age
-------------------------------------------------
1 11 2
Desired output
RoomId Adult Child Infant PropertyId
--------------------------------
1 2 1 1 1
2 2 1 0 1
3 1 0 0 1
sql
sql-server group-by count edited Jan 3 at 14:59
Salman A
185k67343441
asked Jan 3 at 12:44
HarryHarry
225
add a comment |
0
I need some help to solve this query. I have a table which contains the ages of the passengers who are going to stay in a room which is mentioned below:
Age RoomId
----- ---
1 1
12 1
8 1
19 1
3 2
12 2
18 2
21 3
Also, I have properties table which contains the maximum age of the child and maximum age of the infant. Based on the age of the passenger, I need to segregate them to adult, child, and infant to each of the properties.
Properties table structure
Property Id Maximum_child_age Maximum_infant_age
-------------------------------------------------
1 11 2
Desired output
RoomId Adult Child Infant PropertyId
--------------------------------
1 2 1 1 1
2 2 1 0 1
3 1 0 0 1
sql
sql-server group-by count edited Jan 3 at 14:59
Salman A
185k67343441
asked Jan 3 at 12:44
HarryHarry
225
1
And what have you tried so far to achieve this yourself? Please do share your attempts first.
– Larnu
Jan 3 at 12:46
Also, which version of SQL Server? SQL 2008, SQL 2017, etc.
– Sparky
Jan 3 at 12:47
@Sparky : SQL server 2017
– Harry
Jan 3 at 12:49
add a comment |
0
0
0
I need some help to solve this query. I have a table which contains the ages of the passengers who are going to stay in a room which is mentioned below:
Age RoomId
----- ---
1 1
12 1
8 1
19 1
3 2
12 2
18 2
21 3
Also, I have properties table which contains the maximum age of the child and maximum age of the infant. Based on the age of the passenger, I need to segregate them to adult, child, and infant to each of the properties.
Properties table structure
Property Id Maximum_child_age Maximum_infant_age
-------------------------------------------------
1 11 2
Desired output
RoomId Adult Child Infant PropertyId
--------------------------------
1 2 1 1 1
2 2 1 0 1
3 1 0 0 1
sql
sql-server group-by count edited Jan 3 at 14:59
Salman A
185k67343441
asked Jan 3 at 12:44
HarryHarry
225
I need some help to solve this query. I have a table which contains the ages of the passengers who are going to stay in a room which is mentioned below:
Age RoomId
----- ---
1 1
12 1
8 1
19 1
3 2
12 2
18 2
21 3
Also, I have properties table which contains the maximum age of the child and maximum age of the infant. Based on the age of the passenger, I need to segregate them to adult, child, and infant to each of the properties.
Properties table structure
Property Id Maximum_child_age Maximum_infant_age
-------------------------------------------------
1 11 2
Desired output
RoomId Adult Child Infant PropertyId
--------------------------------
1 2 1 1 1
2 2 1 0 1
3 1 0 0 1
sql
sql-server group-by count sql
sql-server group-by count edited Jan 3 at 14:59
Salman A
185k67343441
asked Jan 3 at 12:44
HarryHarry
225
edited Jan 3 at 14:59
Salman A
185k67343441
asked Jan 3 at 12:44
HarryHarry
225
edited Jan 3 at 14:59
Salman A
185k67343441
edited Jan 3 at 14:59
Salman A
185k67343441
edited Jan 3 at 14:59
Salman A
185k67343441
185k67343441
asked Jan 3 at 12:44
HarryHarry
225
asked Jan 3 at 12:44
HarryHarry
225
asked Jan 3 at 12:44
HarryHarry
225
225
1
And what have you tried so far to achieve this yourself? Please do share your attempts first.
– Larnu
Jan 3 at 12:46
Also, which version of SQL Server? SQL 2008, SQL 2017, etc.
– Sparky
Jan 3 at 12:47
@Sparky : SQL server 2017
– Harry
Jan 3 at 12:49
add a comment |
1
And what have you tried so far to achieve this yourself? Please do share your attempts first.
– Larnu
Jan 3 at 12:46
Also, which version of SQL Server? SQL 2008, SQL 2017, etc.
– Sparky
Jan 3 at 12:47
@Sparky : SQL server 2017
– Harry
Jan 3 at 12:49
1
1
And what have you tried so far to achieve this yourself? Please do share your attempts first.
– Larnu
Jan 3 at 12:46
And what have you tried so far to achieve this yourself? Please do share your attempts first.
– Larnu
Jan 3 at 12:46
Also, which version of SQL Server? SQL 2008, SQL 2017, etc.
– Sparky
Jan 3 at 12:47
Also, which version of SQL Server? SQL 2008, SQL 2017, etc.
– Sparky
Jan 3 at 12:47
@Sparky : SQL server 2017
– Harry
Jan 3 at 12:49
@Sparky : SQL server 2017
– Harry
Jan 3 at 12:49
add a comment |
2 Answers
2
active
oldest
votes
3
Use conditional aggregation :
SELECT
SUM(CASE WHEN pas.age > ppt.Maximum_child_age THEN 1 ELSE 0 END) AS Adult,
SUM(CASE WHEN pas.age BETWEEN Maximum_infant_age AND ppt.Maximum_child_age THEN 1 ELSE 0 END) AS Child,
SUM(CASE WHEN pas.age < ppt.Maximum_infant_age THEN 1 ELSE 0 END) AS Infant,
ppt.id
FROM
passengers pas
CROSS JOIN properties ppt
GROUP BY ppt.id
answered Jan 3 at 12:52
GMBGMB
19.5k31028
It gives me an error because of "ON" Keyword, is it ok to use on condition in cross join ?
– Harry
Jan 3 at 13:01
@Harry : sorry that was a typo, I fixed it.
– GMB
Jan 3 at 13:02
Thanks much it worked !!
– Harry
Jan 3 at 13:04
add a comment |
1
Cross join the properties and then do conditional aggregation.
SELECT count(CASE
WHEN pa.ages > pr.maximum_child_age THEN
1
END) adult,
count(CASE
WHEN pa.ages > pr.maximum_infant_age
AND pa.ages <= pr.maximum_child_age THEN
1
END) child,
count(CASE
WHEN pa.ages <= pr.maximum_infant_age THEN
1
END) infant,
pr.propertyid
FROM passengers pa
CROSS JOIN properties pr
GROUP BY pr.propertyid;
answered Jan 3 at 12:55
sticky bitsticky bit
15.2k101733
Thanks for the help !!!
– Harry
Jan 3 at 13:04
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%2f54022566%2ffind-count-group-by-id-in-sql-server%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
3
Use conditional aggregation :
SELECT
SUM(CASE WHEN pas.age > ppt.Maximum_child_age THEN 1 ELSE 0 END) AS Adult,
SUM(CASE WHEN pas.age BETWEEN Maximum_infant_age AND ppt.Maximum_child_age THEN 1 ELSE 0 END) AS Child,
SUM(CASE WHEN pas.age < ppt.Maximum_infant_age THEN 1 ELSE 0 END) AS Infant,
ppt.id
FROM
passengers pas
CROSS JOIN properties ppt
GROUP BY ppt.id
answered Jan 3 at 12:52
GMBGMB
19.5k31028
It gives me an error because of "ON" Keyword, is it ok to use on condition in cross join ?
– Harry
Jan 3 at 13:01
@Harry : sorry that was a typo, I fixed it.
– GMB
Jan 3 at 13:02
Thanks much it worked !!
– Harry
Jan 3 at 13:04
add a comment |
3
Use conditional aggregation :
SELECT
SUM(CASE WHEN pas.age > ppt.Maximum_child_age THEN 1 ELSE 0 END) AS Adult,
SUM(CASE WHEN pas.age BETWEEN Maximum_infant_age AND ppt.Maximum_child_age THEN 1 ELSE 0 END) AS Child,
SUM(CASE WHEN pas.age < ppt.Maximum_infant_age THEN 1 ELSE 0 END) AS Infant,
ppt.id
FROM
passengers pas
CROSS JOIN properties ppt
GROUP BY ppt.id
answered Jan 3 at 12:52
GMBGMB
19.5k31028
It gives me an error because of "ON" Keyword, is it ok to use on condition in cross join ?
– Harry
Jan 3 at 13:01
@Harry : sorry that was a typo, I fixed it.
– GMB
Jan 3 at 13:02
Thanks much it worked !!
– Harry
Jan 3 at 13:04
add a comment |
3
3
3
Use conditional aggregation :
SELECT
SUM(CASE WHEN pas.age > ppt.Maximum_child_age THEN 1 ELSE 0 END) AS Adult,
SUM(CASE WHEN pas.age BETWEEN Maximum_infant_age AND ppt.Maximum_child_age THEN 1 ELSE 0 END) AS Child,
SUM(CASE WHEN pas.age < ppt.Maximum_infant_age THEN 1 ELSE 0 END) AS Infant,
ppt.id
FROM
passengers pas
CROSS JOIN properties ppt
GROUP BY ppt.id
answered Jan 3 at 12:52
GMBGMB
19.5k31028
Use conditional aggregation :
SELECT
SUM(CASE WHEN pas.age > ppt.Maximum_child_age THEN 1 ELSE 0 END) AS Adult,
SUM(CASE WHEN pas.age BETWEEN Maximum_infant_age AND ppt.Maximum_child_age THEN 1 ELSE 0 END) AS Child,
SUM(CASE WHEN pas.age < ppt.Maximum_infant_age THEN 1 ELSE 0 END) AS Infant,
ppt.id
FROM
passengers pas
CROSS JOIN properties ppt
GROUP BY ppt.id
answered Jan 3 at 12:52
GMBGMB
19.5k31028
edited Jan 3 at 13:02
answered Jan 3 at 12:52
GMBGMB
19.5k31028
answered Jan 3 at 12:52
GMBGMB
19.5k31028
answered Jan 3 at 12:52
GMBGMB
19.5k31028
19.5k31028
It gives me an error because of "ON" Keyword, is it ok to use on condition in cross join ?
– Harry
Jan 3 at 13:01
@Harry : sorry that was a typo, I fixed it.
– GMB
Jan 3 at 13:02
Thanks much it worked !!
– Harry
Jan 3 at 13:04
add a comment |
It gives me an error because of "ON" Keyword, is it ok to use on condition in cross join ?
– Harry
Jan 3 at 13:01
@Harry : sorry that was a typo, I fixed it.
– GMB
Jan 3 at 13:02
Thanks much it worked !!
– Harry
Jan 3 at 13:04
It gives me an error because of "ON" Keyword, is it ok to use on condition in cross join ?
– Harry
Jan 3 at 13:01
It gives me an error because of "ON" Keyword, is it ok to use on condition in cross join ?
– Harry
Jan 3 at 13:01
@Harry : sorry that was a typo, I fixed it.
– GMB
Jan 3 at 13:02
@Harry : sorry that was a typo, I fixed it.
– GMB
Jan 3 at 13:02
Thanks much it worked !!
– Harry
Jan 3 at 13:04
Thanks much it worked !!
– Harry
Jan 3 at 13:04
add a comment |
1
Cross join the properties and then do conditional aggregation.
SELECT count(CASE
WHEN pa.ages > pr.maximum_child_age THEN
1
END) adult,
count(CASE
WHEN pa.ages > pr.maximum_infant_age
AND pa.ages <= pr.maximum_child_age THEN
1
END) child,
count(CASE
WHEN pa.ages <= pr.maximum_infant_age THEN
1
END) infant,
pr.propertyid
FROM passengers pa
CROSS JOIN properties pr
GROUP BY pr.propertyid;
answered Jan 3 at 12:55
sticky bitsticky bit
15.2k101733
Thanks for the help !!!
– Harry
Jan 3 at 13:04
add a comment |
1
Cross join the properties and then do conditional aggregation.
SELECT count(CASE
WHEN pa.ages > pr.maximum_child_age THEN
1
END) adult,
count(CASE
WHEN pa.ages > pr.maximum_infant_age
AND pa.ages <= pr.maximum_child_age THEN
1
END) child,
count(CASE
WHEN pa.ages <= pr.maximum_infant_age THEN
1
END) infant,
pr.propertyid
FROM passengers pa
CROSS JOIN properties pr
GROUP BY pr.propertyid;
answered Jan 3 at 12:55
sticky bitsticky bit
15.2k101733
Thanks for the help !!!
– Harry
Jan 3 at 13:04
add a comment |
1
1
1
Cross join the properties and then do conditional aggregation.
SELECT count(CASE
WHEN pa.ages > pr.maximum_child_age THEN
1
END) adult,
count(CASE
WHEN pa.ages > pr.maximum_infant_age
AND pa.ages <= pr.maximum_child_age THEN
1
END) child,
count(CASE
WHEN pa.ages <= pr.maximum_infant_age THEN
1
END) infant,
pr.propertyid
FROM passengers pa
CROSS JOIN properties pr
GROUP BY pr.propertyid;
answered Jan 3 at 12:55
sticky bitsticky bit
15.2k101733
Cross join the properties and then do conditional aggregation.
SELECT count(CASE
WHEN pa.ages > pr.maximum_child_age THEN
1
END) adult,
count(CASE
WHEN pa.ages > pr.maximum_infant_age
AND pa.ages <= pr.maximum_child_age THEN
1
END) child,
count(CASE
WHEN pa.ages <= pr.maximum_infant_age THEN
1
END) infant,
pr.propertyid
FROM passengers pa
CROSS JOIN properties pr
GROUP BY pr.propertyid;
answered Jan 3 at 12:55
sticky bitsticky bit
15.2k101733
answered Jan 3 at 12:55
sticky bitsticky bit
15.2k101733
answered Jan 3 at 12:55
sticky bitsticky bit
15.2k101733
answered Jan 3 at 12:55
sticky bitsticky bit
15.2k101733
15.2k101733
Thanks for the help !!!
– Harry
Jan 3 at 13:04
add a comment |
Thanks for the help !!!
– Harry
Jan 3 at 13:04
Thanks for the help !!!
– Harry
Jan 3 at 13:04
Thanks for the help !!!
– Harry
Jan 3 at 13:04
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%2f54022566%2ffind-count-group-by-id-in-sql-server%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
And what have you tried so far to achieve this yourself? Please do share your attempts first.
– Larnu
Jan 3 at 12:46
Also, which version of SQL Server? SQL 2008, SQL 2017, etc.
– Sparky
Jan 3 at 12:47
@Sparky : SQL server 2017
– Harry
Jan 3 at 12:49