Optimized SQL query with multiple joinsWhat is the difference between “INNER JOIN” and “OUTER JOIN”?How can I prevent SQL injection in PHP?Add a column with a default value to an existing table in SQL ServerHow to concatenate text from multiple rows into a single text string in SQL server?Inserting multiple rows in a single SQL query?Python join: why is it string.join(list) instead of list.join(string)?Difference between JOIN and INNER JOINHow do I UPDATE from a SELECT in SQL Server?Join vs. sub-queryWhat's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?
Can two aircraft be allowed to stay on the same runway at the same time?
Break down the phrase "shitsurei shinakereba naranaindesu"
Understanding data transmission rates over copper wire
Eliminate key lookup in execution plan
Small RAM 4 KB on the early Apple II?
German equivalent to "going down the rabbit hole"
What's the difference between a variable and a memory location?
Moscow SVO airport, how to avoid scam taxis without pre-booking?
Connecting points from separate Tikz figures
Can authors email you PDFs of their textbook for free?
Why are JWST optics not enclosed like HST?
In what language did Túrin converse with Mím?
Calculate Landau's function
Why haven't the British protested Brexit as ardently like Hong Kongers protest?
Lob Logical Read and lob read-ahead reads in NCCI
How do I portray irrational anger in first person?
Storing milk for long periods of time
Should a TA point out a professor's mistake while attending their lecture?
Why does Sauron not permit his followers to use his name?
Printing a list as "a, b, c." using Python
Was it illegal to blaspheme God in Antioch in 360.-410.?
What caused the end of cybernetic implants?
What is the practical impact of using System.Random which is not cryptographically random?
When did coal replace firewood in early America?
Optimized SQL query with multiple joins
What is the difference between “INNER JOIN” and “OUTER JOIN”?How can I prevent SQL injection in PHP?Add a column with a default value to an existing table in SQL ServerHow to concatenate text from multiple rows into a single text string in SQL server?Inserting multiple rows in a single SQL query?Python join: why is it string.join(list) instead of list.join(string)?Difference between JOIN and INNER JOINHow do I UPDATE from a SELECT in SQL Server?Join vs. sub-queryWhat's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I am working on creating a SQL view.
I have below:
- Table A: Customer Account information
- Table B: Account relation to a subscription
- Table C: Amount Paid by Customer for each subscription and other details
- Table D: All Subscription related information
Table A :
AccountNum
Customer_Name
CustomerCode
Table B:
SubscriptionId
SubscriptionName
AccountNum
Table C:
SubscriptionId
AccountNum
AmountPaid
Table D:
SubscriptionId
SubscriptionName
I want to give the input as AccountNum and get Subscription details for his account and amount paid for each subscription for same subscription under different account and subscription details and Account Details in One Query.
Example:
Table A:
AccountNum Customer_Name CustomerCode
1234 TestName TestCode
12345 Testname1 TestCode1
Table B:
SubscriptionId SubscriptionName AccountNum
12 Netflix 1234
12 Netflix 12345
Table C:
SubscriptionId AccountNum AmountPaid
12 1234 100
12 12345 100
Table D:
SubscriptionId SubscriptionName
12 Netflix
13 Prime
So in the above case Account Num 1234 and 12345 is under one customer and both accounts are tied to same subscription. So i am trying to write a query for AccountNum #1234 as input and get Subscription Id, Subscription Name, sum of amount paid for the same subscription from different account under the same customer in one single query.
sql postgresql join
edited Mar 28 at 1:43
DRapp
39.2k10 gold badges61 silver badges118 bronze badges
asked Mar 27 at 20:44
gingging
476 bronze badges
add a comment |
I am working on creating a SQL view.
I have below:
- Table A: Customer Account information
- Table B: Account relation to a subscription
- Table C: Amount Paid by Customer for each subscription and other details
- Table D: All Subscription related information
Table A :
AccountNum
Customer_Name
CustomerCode
Table B:
SubscriptionId
SubscriptionName
AccountNum
Table C:
SubscriptionId
AccountNum
AmountPaid
Table D:
SubscriptionId
SubscriptionName
I want to give the input as AccountNum and get Subscription details for his account and amount paid for each subscription for same subscription under different account and subscription details and Account Details in One Query.
Example:
Table A:
AccountNum Customer_Name CustomerCode
1234 TestName TestCode
12345 Testname1 TestCode1
Table B:
SubscriptionId SubscriptionName AccountNum
12 Netflix 1234
12 Netflix 12345
Table C:
SubscriptionId AccountNum AmountPaid
12 1234 100
12 12345 100
Table D:
SubscriptionId SubscriptionName
12 Netflix
13 Prime
So in the above case Account Num 1234 and 12345 is under one customer and both accounts are tied to same subscription. So i am trying to write a query for AccountNum #1234 as input and get Subscription Id, Subscription Name, sum of amount paid for the same subscription from different account under the same customer in one single query.
sql postgresql join
edited Mar 28 at 1:43
DRapp
39.2k10 gold badges61 silver badges118 bronze badges
asked Mar 27 at 20:44
gingging
476 bronze badges
add a comment |
I am working on creating a SQL view.
I have below:
- Table A: Customer Account information
- Table B: Account relation to a subscription
- Table C: Amount Paid by Customer for each subscription and other details
- Table D: All Subscription related information
Table A :
AccountNum
Customer_Name
CustomerCode
Table B:
SubscriptionId
SubscriptionName
AccountNum
Table C:
SubscriptionId
AccountNum
AmountPaid
Table D:
SubscriptionId
SubscriptionName
I want to give the input as AccountNum and get Subscription details for his account and amount paid for each subscription for same subscription under different account and subscription details and Account Details in One Query.
Example:
Table A:
AccountNum Customer_Name CustomerCode
1234 TestName TestCode
12345 Testname1 TestCode1
Table B:
SubscriptionId SubscriptionName AccountNum
12 Netflix 1234
12 Netflix 12345
Table C:
SubscriptionId AccountNum AmountPaid
12 1234 100
12 12345 100
Table D:
SubscriptionId SubscriptionName
12 Netflix
13 Prime
So in the above case Account Num 1234 and 12345 is under one customer and both accounts are tied to same subscription. So i am trying to write a query for AccountNum #1234 as input and get Subscription Id, Subscription Name, sum of amount paid for the same subscription from different account under the same customer in one single query.
sql postgresql join
edited Mar 28 at 1:43
DRapp
39.2k10 gold badges61 silver badges118 bronze badges
asked Mar 27 at 20:44
gingging
476 bronze badges
I am working on creating a SQL view.
I have below:
- Table A: Customer Account information
- Table B: Account relation to a subscription
- Table C: Amount Paid by Customer for each subscription and other details
- Table D: All Subscription related information
Table A :
AccountNum
Customer_Name
CustomerCode
Table B:
SubscriptionId
SubscriptionName
AccountNum
Table C:
SubscriptionId
AccountNum
AmountPaid
Table D:
SubscriptionId
SubscriptionName
I want to give the input as AccountNum and get Subscription details for his account and amount paid for each subscription for same subscription under different account and subscription details and Account Details in One Query.
Example:
Table A:
AccountNum Customer_Name CustomerCode
1234 TestName TestCode
12345 Testname1 TestCode1
Table B:
SubscriptionId SubscriptionName AccountNum
12 Netflix 1234
12 Netflix 12345
Table C:
SubscriptionId AccountNum AmountPaid
12 1234 100
12 12345 100
Table D:
SubscriptionId SubscriptionName
12 Netflix
13 Prime
So in the above case Account Num 1234 and 12345 is under one customer and both accounts are tied to same subscription. So i am trying to write a query for AccountNum #1234 as input and get Subscription Id, Subscription Name, sum of amount paid for the same subscription from different account under the same customer in one single query.
sql postgresql join
sql postgresql join
edited Mar 28 at 1:43
DRapp
39.2k10 gold badges61 silver badges118 bronze badges
asked Mar 27 at 20:44
gingging
476 bronze badges
edited Mar 28 at 1:43
DRapp
39.2k10 gold badges61 silver badges118 bronze badges
asked Mar 27 at 20:44
gingging
476 bronze badges
edited Mar 28 at 1:43
DRapp
39.2k10 gold badges61 silver badges118 bronze badges
edited Mar 28 at 1:43
DRapp
39.2k10 gold badges61 silver badges118 bronze badges
edited Mar 28 at 1:43
DRapp
39.2k10 gold badges61 silver badges118 bronze badges
39.2k10 gold badges61 silver badges118 bronze badges
asked Mar 27 at 20:44
gingging
476 bronze badges
asked Mar 27 at 20:44
gingging
476 bronze badges
asked Mar 27 at 20:44
gingging
476 bronze badges
476 bronze badges
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
You can't perform an aggregate function and retrieve fields which are not used in 'group by' in same query as it contradicts
You can use below query to find total amount spent per customer
select
a.CustomerCode,
sum(case when c.AmountPaid is not null
then c.AmountPaid else 0 end) as TotalAmtPaid
from a
inner join b
on a.AccountNum=b.AccountNum
inner join c
on a.AccountNum=c.AccountNum
where
a.AccountNum = ?
group by
a.CustCode;
if you want subscription details as well then multiple rows will need to be displayed with total amount in each row, which makes no sense
edited Mar 28 at 1:36
DRapp
39.2k10 gold badges61 silver badges118 bronze badges
answered Mar 27 at 23:05
abhishekabhishek
112 bronze badges
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%2f55386133%2foptimized-sql-query-with-multiple-joins%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
You can't perform an aggregate function and retrieve fields which are not used in 'group by' in same query as it contradicts
You can use below query to find total amount spent per customer
select
a.CustomerCode,
sum(case when c.AmountPaid is not null
then c.AmountPaid else 0 end) as TotalAmtPaid
from a
inner join b
on a.AccountNum=b.AccountNum
inner join c
on a.AccountNum=c.AccountNum
where
a.AccountNum = ?
group by
a.CustCode;
if you want subscription details as well then multiple rows will need to be displayed with total amount in each row, which makes no sense
edited Mar 28 at 1:36
DRapp
39.2k10 gold badges61 silver badges118 bronze badges
answered Mar 27 at 23:05
abhishekabhishek
112 bronze badges
add a comment |
You can't perform an aggregate function and retrieve fields which are not used in 'group by' in same query as it contradicts
You can use below query to find total amount spent per customer
select
a.CustomerCode,
sum(case when c.AmountPaid is not null
then c.AmountPaid else 0 end) as TotalAmtPaid
from a
inner join b
on a.AccountNum=b.AccountNum
inner join c
on a.AccountNum=c.AccountNum
where
a.AccountNum = ?
group by
a.CustCode;
if you want subscription details as well then multiple rows will need to be displayed with total amount in each row, which makes no sense
edited Mar 28 at 1:36
DRapp
39.2k10 gold badges61 silver badges118 bronze badges
answered Mar 27 at 23:05
abhishekabhishek
112 bronze badges
add a comment |
You can't perform an aggregate function and retrieve fields which are not used in 'group by' in same query as it contradicts
You can use below query to find total amount spent per customer
select
a.CustomerCode,
sum(case when c.AmountPaid is not null
then c.AmountPaid else 0 end) as TotalAmtPaid
from a
inner join b
on a.AccountNum=b.AccountNum
inner join c
on a.AccountNum=c.AccountNum
where
a.AccountNum = ?
group by
a.CustCode;
if you want subscription details as well then multiple rows will need to be displayed with total amount in each row, which makes no sense
edited Mar 28 at 1:36
DRapp
39.2k10 gold badges61 silver badges118 bronze badges
answered Mar 27 at 23:05
abhishekabhishek
112 bronze badges
You can't perform an aggregate function and retrieve fields which are not used in 'group by' in same query as it contradicts
You can use below query to find total amount spent per customer
select
a.CustomerCode,
sum(case when c.AmountPaid is not null
then c.AmountPaid else 0 end) as TotalAmtPaid
from a
inner join b
on a.AccountNum=b.AccountNum
inner join c
on a.AccountNum=c.AccountNum
where
a.AccountNum = ?
group by
a.CustCode;
if you want subscription details as well then multiple rows will need to be displayed with total amount in each row, which makes no sense
edited Mar 28 at 1:36
DRapp
39.2k10 gold badges61 silver badges118 bronze badges
answered Mar 27 at 23:05
abhishekabhishek
112 bronze badges
edited Mar 28 at 1:36
DRapp
39.2k10 gold badges61 silver badges118 bronze badges
edited Mar 28 at 1:36
DRapp
39.2k10 gold badges61 silver badges118 bronze badges
edited Mar 28 at 1:36
DRapp
39.2k10 gold badges61 silver badges118 bronze badges
39.2k10 gold badges61 silver badges118 bronze badges
answered Mar 27 at 23:05
abhishekabhishek
112 bronze badges
answered Mar 27 at 23:05
abhishekabhishek
112 bronze badges
answered Mar 27 at 23:05
abhishekabhishek
112 bronze badges
112 bronze badges
add a comment |
add a comment |
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
Got a question that you can’t ask on public Stack Overflow? Learn more about sharing private information with Stack Overflow for Teams.
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%2f55386133%2foptimized-sql-query-with-multiple-joins%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
