MYSQL: Returning multiple counts from multiple tables without joining
0
accounts table has account_id, account_type, date_opened.
loans table has account_id, balance.
to select the number of new loans:
select count(a.account_id) from account a
where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH)
and a.account_type = 'L'
to select the number of open loans:
select count(*) from loans l
where l.balance > 0
or
SUM(CASE WHEN l.balance > 0 THEN 1 ELSE 0 END) as loans_opened
How to do this in a single select? Things I've tried:
select
count(a.account_id),
SUM(CASE WHEN l.balance > 0 THEN 1 ELSE 0 END) as loans_opened
from account a as new_loans,
LEFT JOIN loans l ON l.account_id = a.account_id
where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH) and a.account_type = 'L'
returns 0 loans
select
count(a.account_id) as new_loans,
count(l.account_id) as loans_opened
from account a as new_loans,
LEFT JOIN loans l ON l.account_id IN (SELECT account_id from account) and balance > 0
where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH) and a.account_type = 'L'
query never returns
Is there a way to do this with or without joins?
mysql
edited Dec 28 '18 at 16:40
Barmar
421k35245346
asked Dec 28 '18 at 16:20
droideckardroideckar
1,034917
add a comment |
0
accounts table has account_id, account_type, date_opened.
loans table has account_id, balance.
to select the number of new loans:
select count(a.account_id) from account a
where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH)
and a.account_type = 'L'
to select the number of open loans:
select count(*) from loans l
where l.balance > 0
or
SUM(CASE WHEN l.balance > 0 THEN 1 ELSE 0 END) as loans_opened
How to do this in a single select? Things I've tried:
select
count(a.account_id),
SUM(CASE WHEN l.balance > 0 THEN 1 ELSE 0 END) as loans_opened
from account a as new_loans,
LEFT JOIN loans l ON l.account_id = a.account_id
where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH) and a.account_type = 'L'
returns 0 loans
select
count(a.account_id) as new_loans,
count(l.account_id) as loans_opened
from account a as new_loans,
LEFT JOIN loans l ON l.account_id IN (SELECT account_id from account) and balance > 0
where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH) and a.account_type = 'L'
query never returns
Is there a way to do this with or without joins?
mysql
edited Dec 28 '18 at 16:40
Barmar
421k35245346
asked Dec 28 '18 at 16:20
droideckardroideckar
1,034917
1
The result could easily be returned with a single statement, but the statement would have multiple SELECT keywords. But given the requirement "to do this in a single select", the only way to do that is with an ugly cross join (which will only work if both tables are guaranteed to be non-empty) and conditional aggregation. What is the reason for the requirement of using only a single SELECT keyword?
– spencer7593
Dec 28 '18 at 16:37
Ok let me rephrase the question. Can this be done in a single SELECT statement which may contain sub-queries including additional SELECT phrases?
– droideckar
Dec 28 '18 at 19:28
1
a SELECT query that returns a scalar (i.e. a single value, the result set is a single row with a single column) ... that subquery can be incorporated into the SELECT list of an outer query. As a simple demonstration of the pattern:SELECT (SELECT 1) AS c1, (SELECT 2) AS c2. That pattern can be extended with more complex subqueries e.g.SELECT ( SELECT COUNT(*) FROM ... ) AS c1, ( SELECT SUM( IF(l.balance > 0, 1, 0) ) FROM ... l ) AS c2
– spencer7593
Jan 2 at 15:44
add a comment |
0
0
0
accounts table has account_id, account_type, date_opened.
loans table has account_id, balance.
to select the number of new loans:
select count(a.account_id) from account a
where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH)
and a.account_type = 'L'
to select the number of open loans:
select count(*) from loans l
where l.balance > 0
or
SUM(CASE WHEN l.balance > 0 THEN 1 ELSE 0 END) as loans_opened
How to do this in a single select? Things I've tried:
select
count(a.account_id),
SUM(CASE WHEN l.balance > 0 THEN 1 ELSE 0 END) as loans_opened
from account a as new_loans,
LEFT JOIN loans l ON l.account_id = a.account_id
where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH) and a.account_type = 'L'
returns 0 loans
select
count(a.account_id) as new_loans,
count(l.account_id) as loans_opened
from account a as new_loans,
LEFT JOIN loans l ON l.account_id IN (SELECT account_id from account) and balance > 0
where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH) and a.account_type = 'L'
query never returns
Is there a way to do this with or without joins?
mysql
edited Dec 28 '18 at 16:40
Barmar
421k35245346
asked Dec 28 '18 at 16:20
droideckardroideckar
1,034917
accounts table has account_id, account_type, date_opened.
loans table has account_id, balance.
to select the number of new loans:
select count(a.account_id) from account a
where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH)
and a.account_type = 'L'
to select the number of open loans:
select count(*) from loans l
where l.balance > 0
or
SUM(CASE WHEN l.balance > 0 THEN 1 ELSE 0 END) as loans_opened
How to do this in a single select? Things I've tried:
select
count(a.account_id),
SUM(CASE WHEN l.balance > 0 THEN 1 ELSE 0 END) as loans_opened
from account a as new_loans,
LEFT JOIN loans l ON l.account_id = a.account_id
where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH) and a.account_type = 'L'
returns 0 loans
select
count(a.account_id) as new_loans,
count(l.account_id) as loans_opened
from account a as new_loans,
LEFT JOIN loans l ON l.account_id IN (SELECT account_id from account) and balance > 0
where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH) and a.account_type = 'L'
query never returns
Is there a way to do this with or without joins?
mysql
mysql
edited Dec 28 '18 at 16:40
Barmar
421k35245346
asked Dec 28 '18 at 16:20
droideckardroideckar
1,034917
edited Dec 28 '18 at 16:40
Barmar
421k35245346
asked Dec 28 '18 at 16:20
droideckardroideckar
1,034917
edited Dec 28 '18 at 16:40
Barmar
421k35245346
edited Dec 28 '18 at 16:40
Barmar
421k35245346
edited Dec 28 '18 at 16:40
Barmar
421k35245346
421k35245346
asked Dec 28 '18 at 16:20
droideckardroideckar
1,034917
asked Dec 28 '18 at 16:20
droideckardroideckar
1,034917
asked Dec 28 '18 at 16:20
droideckardroideckar
1,034917
1,034917
1
The result could easily be returned with a single statement, but the statement would have multiple SELECT keywords. But given the requirement "to do this in a single select", the only way to do that is with an ugly cross join (which will only work if both tables are guaranteed to be non-empty) and conditional aggregation. What is the reason for the requirement of using only a single SELECT keyword?
– spencer7593
Dec 28 '18 at 16:37
Ok let me rephrase the question. Can this be done in a single SELECT statement which may contain sub-queries including additional SELECT phrases?
– droideckar
Dec 28 '18 at 19:28
1
a SELECT query that returns a scalar (i.e. a single value, the result set is a single row with a single column) ... that subquery can be incorporated into the SELECT list of an outer query. As a simple demonstration of the pattern:SELECT (SELECT 1) AS c1, (SELECT 2) AS c2. That pattern can be extended with more complex subqueries e.g.SELECT ( SELECT COUNT(*) FROM ... ) AS c1, ( SELECT SUM( IF(l.balance > 0, 1, 0) ) FROM ... l ) AS c2
– spencer7593
Jan 2 at 15:44
add a comment |
1
The result could easily be returned with a single statement, but the statement would have multiple SELECT keywords. But given the requirement "to do this in a single select", the only way to do that is with an ugly cross join (which will only work if both tables are guaranteed to be non-empty) and conditional aggregation. What is the reason for the requirement of using only a single SELECT keyword?
– spencer7593
Dec 28 '18 at 16:37
Ok let me rephrase the question. Can this be done in a single SELECT statement which may contain sub-queries including additional SELECT phrases?
– droideckar
Dec 28 '18 at 19:28
1
a SELECT query that returns a scalar (i.e. a single value, the result set is a single row with a single column) ... that subquery can be incorporated into the SELECT list of an outer query. As a simple demonstration of the pattern:SELECT (SELECT 1) AS c1, (SELECT 2) AS c2. That pattern can be extended with more complex subqueries e.g.SELECT ( SELECT COUNT(*) FROM ... ) AS c1, ( SELECT SUM( IF(l.balance > 0, 1, 0) ) FROM ... l ) AS c2
– spencer7593
Jan 2 at 15:44
1
1
The result could easily be returned with a single statement, but the statement would have multiple SELECT keywords. But given the requirement "to do this in a single select", the only way to do that is with an ugly cross join (which will only work if both tables are guaranteed to be non-empty) and conditional aggregation. What is the reason for the requirement of using only a single SELECT keyword?
– spencer7593
Dec 28 '18 at 16:37
The result could easily be returned with a single statement, but the statement would have multiple SELECT keywords. But given the requirement "to do this in a single select", the only way to do that is with an ugly cross join (which will only work if both tables are guaranteed to be non-empty) and conditional aggregation. What is the reason for the requirement of using only a single SELECT keyword?
– spencer7593
Dec 28 '18 at 16:37
Ok let me rephrase the question. Can this be done in a single SELECT statement which may contain sub-queries including additional SELECT phrases?
– droideckar
Dec 28 '18 at 19:28
Ok let me rephrase the question. Can this be done in a single SELECT statement which may contain sub-queries including additional SELECT phrases?
– droideckar
Dec 28 '18 at 19:28
1
1
a SELECT query that returns a scalar (i.e. a single value, the result set is a single row with a single column) ... that subquery can be incorporated into the SELECT list of an outer query. As a simple demonstration of the pattern:
SELECT (SELECT 1) AS c1, (SELECT 2) AS c2. That pattern can be extended with more complex subqueries e.g. SELECT ( SELECT COUNT(*) FROM ... ) AS c1, ( SELECT SUM( IF(l.balance > 0, 1, 0) ) FROM ... l ) AS c2– spencer7593
Jan 2 at 15:44
a SELECT query that returns a scalar (i.e. a single value, the result set is a single row with a single column) ... that subquery can be incorporated into the SELECT list of an outer query. As a simple demonstration of the pattern:
SELECT (SELECT 1) AS c1, (SELECT 2) AS c2. That pattern can be extended with more complex subqueries e.g. SELECT ( SELECT COUNT(*) FROM ... ) AS c1, ( SELECT SUM( IF(l.balance > 0, 1, 0) ) FROM ... l ) AS c2– spencer7593
Jan 2 at 15:44
add a comment |
2 Answers
2
active
oldest
votes
1
You can do it by using the two SELECT queries as subqueries in the main query's SELECT list:
SELECT (SELECT COUNT(*)
FROM account
WHERE date_opened > LAST_DAY(NOW() - INTERVAL 1 MONTH)
AND account_type = 'L') AS new_loans,
(SELECT COUNT(*)
FROM loans
WHERE balance > 0) AS open_loans;
answered Dec 28 '18 at 16:43
BarmarBarmar
421k35245346
Here is a test: db-fiddle.com/f/whouqAd8RGtDaCoRF7HtBt/7 I tryed a solution with LEFT JOIN, but your solution have better results :D
– Steffen Mächtel
Dec 28 '18 at 16:48
add a comment |
0
did you try something like this?
select (select count(*) from loans l where l.balance > 0) loans_opened,(select count(a.account_id) from account a where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH) and a.account_type = 'L') new_loans;
answered Dec 28 '18 at 16:45
ricardosalazarfullstackricardosalazarfullstack
524
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%2f53961374%2fmysql-returning-multiple-counts-from-multiple-tables-without-joining%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
1
You can do it by using the two SELECT queries as subqueries in the main query's SELECT list:
SELECT (SELECT COUNT(*)
FROM account
WHERE date_opened > LAST_DAY(NOW() - INTERVAL 1 MONTH)
AND account_type = 'L') AS new_loans,
(SELECT COUNT(*)
FROM loans
WHERE balance > 0) AS open_loans;
answered Dec 28 '18 at 16:43
BarmarBarmar
421k35245346
Here is a test: db-fiddle.com/f/whouqAd8RGtDaCoRF7HtBt/7 I tryed a solution with LEFT JOIN, but your solution have better results :D
– Steffen Mächtel
Dec 28 '18 at 16:48
add a comment |
1
You can do it by using the two SELECT queries as subqueries in the main query's SELECT list:
SELECT (SELECT COUNT(*)
FROM account
WHERE date_opened > LAST_DAY(NOW() - INTERVAL 1 MONTH)
AND account_type = 'L') AS new_loans,
(SELECT COUNT(*)
FROM loans
WHERE balance > 0) AS open_loans;
answered Dec 28 '18 at 16:43
BarmarBarmar
421k35245346
Here is a test: db-fiddle.com/f/whouqAd8RGtDaCoRF7HtBt/7 I tryed a solution with LEFT JOIN, but your solution have better results :D
– Steffen Mächtel
Dec 28 '18 at 16:48
add a comment |
1
1
1
You can do it by using the two SELECT queries as subqueries in the main query's SELECT list:
SELECT (SELECT COUNT(*)
FROM account
WHERE date_opened > LAST_DAY(NOW() - INTERVAL 1 MONTH)
AND account_type = 'L') AS new_loans,
(SELECT COUNT(*)
FROM loans
WHERE balance > 0) AS open_loans;
answered Dec 28 '18 at 16:43
BarmarBarmar
421k35245346
You can do it by using the two SELECT queries as subqueries in the main query's SELECT list:
SELECT (SELECT COUNT(*)
FROM account
WHERE date_opened > LAST_DAY(NOW() - INTERVAL 1 MONTH)
AND account_type = 'L') AS new_loans,
(SELECT COUNT(*)
FROM loans
WHERE balance > 0) AS open_loans;
answered Dec 28 '18 at 16:43
BarmarBarmar
421k35245346
edited Dec 28 '18 at 16:51
answered Dec 28 '18 at 16:43
BarmarBarmar
421k35245346
answered Dec 28 '18 at 16:43
BarmarBarmar
421k35245346
answered Dec 28 '18 at 16:43
BarmarBarmar
421k35245346
421k35245346
Here is a test: db-fiddle.com/f/whouqAd8RGtDaCoRF7HtBt/7 I tryed a solution with LEFT JOIN, but your solution have better results :D
– Steffen Mächtel
Dec 28 '18 at 16:48
add a comment |
Here is a test: db-fiddle.com/f/whouqAd8RGtDaCoRF7HtBt/7 I tryed a solution with LEFT JOIN, but your solution have better results :D
– Steffen Mächtel
Dec 28 '18 at 16:48
Here is a test: db-fiddle.com/f/whouqAd8RGtDaCoRF7HtBt/7 I tryed a solution with LEFT JOIN, but your solution have better results :D
– Steffen Mächtel
Dec 28 '18 at 16:48
Here is a test: db-fiddle.com/f/whouqAd8RGtDaCoRF7HtBt/7 I tryed a solution with LEFT JOIN, but your solution have better results :D
– Steffen Mächtel
Dec 28 '18 at 16:48
add a comment |
0
did you try something like this?
select (select count(*) from loans l where l.balance > 0) loans_opened,(select count(a.account_id) from account a where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH) and a.account_type = 'L') new_loans;
answered Dec 28 '18 at 16:45
ricardosalazarfullstackricardosalazarfullstack
524
add a comment |
0
did you try something like this?
select (select count(*) from loans l where l.balance > 0) loans_opened,(select count(a.account_id) from account a where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH) and a.account_type = 'L') new_loans;
answered Dec 28 '18 at 16:45
ricardosalazarfullstackricardosalazarfullstack
524
add a comment |
0
0
0
did you try something like this?
select (select count(*) from loans l where l.balance > 0) loans_opened,(select count(a.account_id) from account a where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH) and a.account_type = 'L') new_loans;
answered Dec 28 '18 at 16:45
ricardosalazarfullstackricardosalazarfullstack
524
did you try something like this?
select (select count(*) from loans l where l.balance > 0) loans_opened,(select count(a.account_id) from account a where a.date_opened > LAST_DAY(now() - INTERVAL 1 MONTH) and a.account_type = 'L') new_loans;
answered Dec 28 '18 at 16:45
ricardosalazarfullstackricardosalazarfullstack
524
answered Dec 28 '18 at 16:45
ricardosalazarfullstackricardosalazarfullstack
524
answered Dec 28 '18 at 16:45
ricardosalazarfullstackricardosalazarfullstack
524
answered Dec 28 '18 at 16:45
ricardosalazarfullstackricardosalazarfullstack
524
524
add a comment |
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%2f53961374%2fmysql-returning-multiple-counts-from-multiple-tables-without-joining%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
The result could easily be returned with a single statement, but the statement would have multiple SELECT keywords. But given the requirement "to do this in a single select", the only way to do that is with an ugly cross join (which will only work if both tables are guaranteed to be non-empty) and conditional aggregation. What is the reason for the requirement of using only a single SELECT keyword?
– spencer7593
Dec 28 '18 at 16:37
Ok let me rephrase the question. Can this be done in a single SELECT statement which may contain sub-queries including additional SELECT phrases?
– droideckar
Dec 28 '18 at 19:28
1
a SELECT query that returns a scalar (i.e. a single value, the result set is a single row with a single column) ... that subquery can be incorporated into the SELECT list of an outer query. As a simple demonstration of the pattern:
SELECT (SELECT 1) AS c1, (SELECT 2) AS c2. That pattern can be extended with more complex subqueries e.g.SELECT ( SELECT COUNT(*) FROM ... ) AS c1, ( SELECT SUM( IF(l.balance > 0, 1, 0) ) FROM ... l ) AS c2– spencer7593
Jan 2 at 15:44