Styjun

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;

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

share|improve this question

edited Mar 28 at 1:43

DRapp

39.2k1010 gold badges6161 silver badges118118 bronze badges

asked Mar 27 at 20:44

gingging

4766 bronze badges

add a comment | 

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

share|improve this question

edited Mar 28 at 1:43

DRapp

39.2k1010 gold badges6161 silver badges118118 bronze badges

asked Mar 27 at 20:44

gingging

4766 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

share|improve this question

edited Mar 28 at 1:43

DRapp

39.2k1010 gold badges6161 silver badges118118 bronze badges

asked Mar 27 at 20:44

gingging

4766 bronze badges

AccountNum
Customer_Name
CustomerCode

SubscriptionId
SubscriptionName
AccountNum

SubscriptionId
AccountNum
AmountPaid

SubscriptionId
SubscriptionName

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

share|improve this question

edited Mar 28 at 1:43

DRapp

39.2k1010 gold badges6161 silver badges118118 bronze badges

asked Mar 27 at 20:44

gingging

4766 bronze badges

share|improve this question

edited Mar 28 at 1:43

DRapp

39.2k1010 gold badges6161 silver badges118118 bronze badges

asked Mar 27 at 20:44

gingging

4766 bronze badges

edited Mar 28 at 1:43

DRapp

39.2k1010 gold badges6161 silver badges118118 bronze badges

edited Mar 28 at 1:43

DRapp

39.2k1010 gold badges6161 silver badges118118 bronze badges

asked Mar 27 at 20:44

gingging

4766 bronze badges

asked Mar 27 at 20:44

gingging

4766 bronze badges

1 Answer
1

active

oldest

votes

0

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

share|improve this answer

edited Mar 28 at 1:36

DRapp

39.2k1010 gold badges6161 silver badges118118 bronze badges

answered Mar 27 at 23:05

abhishekabhishek

1122 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
);



);

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

Name

Email

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

Name

Email

Required, but never shown

0

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

share|improve this answer

edited Mar 28 at 1:36

DRapp

39.2k1010 gold badges6161 silver badges118118 bronze badges

answered Mar 27 at 23:05

abhishekabhishek

1122 bronze badges

add a comment | 

0

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

share|improve this answer

edited Mar 28 at 1:36

DRapp

39.2k1010 gold badges6161 silver badges118118 bronze badges

answered Mar 27 at 23:05

abhishekabhishek

1122 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

share|improve this answer

edited Mar 28 at 1:36

DRapp

39.2k1010 gold badges6161 silver badges118118 bronze badges

answered Mar 27 at 23:05

abhishekabhishek

1122 bronze badges

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;

share|improve this answer

edited Mar 28 at 1:36

DRapp

39.2k1010 gold badges6161 silver badges118118 bronze badges

answered Mar 27 at 23:05

abhishekabhishek

1122 bronze badges

edited Mar 28 at 1:36

DRapp

39.2k1010 gold badges6161 silver badges118118 bronze badges

edited Mar 28 at 1:36

DRapp

39.2k1010 gold badges6161 silver badges118118 bronze badges

answered Mar 27 at 23:05

abhishekabhishek

1122 bronze badges

answered Mar 27 at 23:05

abhishekabhishek

1122 bronze badges