Styjun

Read string of any length in C

Why is it called a stateful and a stateless firewall?

Why any infinite sequence of real functions can be generated from a finite set through composition?

Teleport everything in a large zone; or teleport all living things and make a lot of equipment disappear

Prove that a convergent real sequence always has a smallest or a largest term

How does a simple logistic regression model achieve a 92% classification accuracy on MNIST?

How to draw a Venn diagram for X - (Y intersect Z)?

What does "boys rule, girls drool" mean?

Why is the year in this ISO timestamp not 2019?

Why does the speed of sound decrease at high altitudes although the air density decreases?

How can I prevent my AC condensate pipe from making my soil soggy?

Permutations in Disguise

Are there objective criteria for classifying consonance v. dissonance?

2000s space film where an alien species has almost wiped out the human race in a war

How do certain apps show new notifications when internet access is restricted to them?

Is my sink P-trap too low?

Output a Super Mario Image

Is there a wall log?

Ambiguity in notation resolved by +

Bash awk command with quotes

If I want an interpretable model, are there methods other than Linear Regression?

'Overwrote' files, space still occupied, are they lost?

Difference between system uptime and last boot time in windows

What is a "major country" as named in Bernie Sanders' Healthcare debate answers?

How do i join and sum values in 3 tables where i need to group by one key column

Add a column with a default value to an existing table in SQL ServerHow to check if a column exists in a SQL Server table?How can I list all foreign keys referencing a given table in SQL Server?How can I get column names from a table in SQL Server?How to JOIN to a table that has multiple values in the column?How to use Update Select statement based on more tables in SQL Server?SQL Server Conditional JOIN two tablesEntity Framework 6 Code First, Composite Foreign KeySQL Server: Insert nvarchar values from one table into int column of another tableSQL Logical Key

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

1

I'm trying to join 3 tables(tableA,tableB,tableC) all have 3 fields(invoice,productClass,value).tableA and tableB dont have values in productClass field and tableC does not have values in Value field. All tables have common value Invoice but different ProductClass and the result should sum values based on invoices and ProductClass

create table tableA (
 Invoice varchar(100),
 ProductClass varchar(100),
 Value int
)
create table tableB (
 Invoice varchar(100),
 ProductClass varchar(100),
 Value int
)
create table tableC (
 Invoice varchar(100),
 ProductClass varchar(100),
 Value int
)

insert into tableA
values
('001','',10),
('002','',20),
('003','',30),
('004','',40)


insert into tableB
values
('001','',20),
('002','',30),
('003','',40),
('004','',50)


insert into tableC
values
('001','aaa',null),
('002','aab',null),
('003','aac',null),
('004','aad',null)



select Invoice,ProductClass,sum(Value)
from(
 select * from tableA
 union
 select * from tableB
 union
 select * from tableC
)tableD
group by Invoice,ProductClass

i expect the results to return

Invoice - 001,002,003,004
ProductClass - aaa,aab,aac,aad
Value - 30,50,70,90

sql-server tsql

share|improve this question

edited Mar 28 at 12:14

Yogesh Sharma

36.9k55 gold badges1515 silver badges4040 bronze badges

asked Mar 28 at 12:06

RonaldRonald

1555 bronze badges

add a comment
 | 

1

I'm trying to join 3 tables(tableA,tableB,tableC) all have 3 fields(invoice,productClass,value).tableA and tableB dont have values in productClass field and tableC does not have values in Value field. All tables have common value Invoice but different ProductClass and the result should sum values based on invoices and ProductClass

create table tableA (
 Invoice varchar(100),
 ProductClass varchar(100),
 Value int
)
create table tableB (
 Invoice varchar(100),
 ProductClass varchar(100),
 Value int
)
create table tableC (
 Invoice varchar(100),
 ProductClass varchar(100),
 Value int
)

insert into tableA
values
('001','',10),
('002','',20),
('003','',30),
('004','',40)


insert into tableB
values
('001','',20),
('002','',30),
('003','',40),
('004','',50)


insert into tableC
values
('001','aaa',null),
('002','aab',null),
('003','aac',null),
('004','aad',null)



select Invoice,ProductClass,sum(Value)
from(
 select * from tableA
 union
 select * from tableB
 union
 select * from tableC
)tableD
group by Invoice,ProductClass

i expect the results to return

Invoice - 001,002,003,004
ProductClass - aaa,aab,aac,aad
Value - 30,50,70,90

sql-server tsql

share|improve this question

edited Mar 28 at 12:14

Yogesh Sharma

36.9k55 gold badges1515 silver badges4040 bronze badges

asked Mar 28 at 12:06

RonaldRonald

1555 bronze badges

add a comment
 | 

I'm trying to join 3 tables(tableA,tableB,tableC) all have 3 fields(invoice,productClass,value).tableA and tableB dont have values in productClass field and tableC does not have values in Value field. All tables have common value Invoice but different ProductClass and the result should sum values based on invoices and ProductClass

create table tableA (
 Invoice varchar(100),
 ProductClass varchar(100),
 Value int
)
create table tableB (
 Invoice varchar(100),
 ProductClass varchar(100),
 Value int
)
create table tableC (
 Invoice varchar(100),
 ProductClass varchar(100),
 Value int
)

insert into tableA
values
('001','',10),
('002','',20),
('003','',30),
('004','',40)


insert into tableB
values
('001','',20),
('002','',30),
('003','',40),
('004','',50)


insert into tableC
values
('001','aaa',null),
('002','aab',null),
('003','aac',null),
('004','aad',null)



select Invoice,ProductClass,sum(Value)
from(
 select * from tableA
 union
 select * from tableB
 union
 select * from tableC
)tableD
group by Invoice,ProductClass

i expect the results to return

Invoice - 001,002,003,004
ProductClass - aaa,aab,aac,aad
Value - 30,50,70,90

sql-server tsql

share|improve this question

edited Mar 28 at 12:14

Yogesh Sharma

36.9k55 gold badges1515 silver badges4040 bronze badges

asked Mar 28 at 12:06

RonaldRonald

1555 bronze badges

create table tableA (
 Invoice varchar(100),
 ProductClass varchar(100),
 Value int
)
create table tableB (
 Invoice varchar(100),
 ProductClass varchar(100),
 Value int
)
create table tableC (
 Invoice varchar(100),
 ProductClass varchar(100),
 Value int
)

insert into tableA
values
('001','',10),
('002','',20),
('003','',30),
('004','',40)


insert into tableB
values
('001','',20),
('002','',30),
('003','',40),
('004','',50)


insert into tableC
values
('001','aaa',null),
('002','aab',null),
('003','aac',null),
('004','aad',null)



select Invoice,ProductClass,sum(Value)
from(
 select * from tableA
 union
 select * from tableB
 union
 select * from tableC
)tableD
group by Invoice,ProductClass

Invoice - 001,002,003,004
ProductClass - aaa,aab,aac,aad
Value - 30,50,70,90

sql-server tsql

sql-server tsql

share|improve this question

edited Mar 28 at 12:14

Yogesh Sharma

36.9k55 gold badges1515 silver badges4040 bronze badges

asked Mar 28 at 12:06

RonaldRonald

1555 bronze badges

share|improve this question

edited Mar 28 at 12:14

Yogesh Sharma

36.9k55 gold badges1515 silver badges4040 bronze badges

asked Mar 28 at 12:06

RonaldRonald

1555 bronze badges

edited Mar 28 at 12:14

Yogesh Sharma

36.9k55 gold badges1515 silver badges4040 bronze badges

edited Mar 28 at 12:14

Yogesh Sharma

36.9k55 gold badges1515 silver badges4040 bronze badges

asked Mar 28 at 12:06

RonaldRonald

1555 bronze badges

asked Mar 28 at 12:06

RonaldRonald

1555 bronze badges

1 Answer
1

active

oldest

votes

1

You can use max() for ProductClass if it has value like blank or nulls:

select Invoice, max(ProductClass) as ProductClass, sum(Value) as Value
from(select * from tableA
 union
 select * from tableB
 union
 select * from tableC
 )tableD
group by Invoice;

share|improve this answer

answered Mar 28 at 12:12

Yogesh SharmaYogesh Sharma

36.9k55 gold badges1515 silver badges4040 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  ProductClass does not have blank or null values check tableC on the sample code that i posted
  
  – Ronald
  Mar 28 at 12:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Ronald . . Yes tableC don't have a null or blank but first two table have this. So, if you are using this column in GROUP BY it will considered as separate group i.e (blank). So, for that your result would be fine. If you want single group then use max() in ProductClass.
  
  – Yogesh Sharma
  Mar 28 at 12:24
  
  

  
  
  
  

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/4.0/"u003ecc by-sa 4.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%2f55397246%2fhow-do-i-join-and-sum-values-in-3-tables-where-i-need-to-group-by-one-key-column%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

1

You can use max() for ProductClass if it has value like blank or nulls:

select Invoice, max(ProductClass) as ProductClass, sum(Value) as Value
from(select * from tableA
 union
 select * from tableB
 union
 select * from tableC
 )tableD
group by Invoice;

share|improve this answer

answered Mar 28 at 12:12

Yogesh SharmaYogesh Sharma

36.9k55 gold badges1515 silver badges4040 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  ProductClass does not have blank or null values check tableC on the sample code that i posted
  
  – Ronald
  Mar 28 at 12:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Ronald . . Yes tableC don't have a null or blank but first two table have this. So, if you are using this column in GROUP BY it will considered as separate group i.e (blank). So, for that your result would be fine. If you want single group then use max() in ProductClass.
  
  – Yogesh Sharma
  Mar 28 at 12:24
  
  

  
  
  
  

add a comment
 | 

1

You can use max() for ProductClass if it has value like blank or nulls:

select Invoice, max(ProductClass) as ProductClass, sum(Value) as Value
from(select * from tableA
 union
 select * from tableB
 union
 select * from tableC
 )tableD
group by Invoice;

share|improve this answer

answered Mar 28 at 12:12

Yogesh SharmaYogesh Sharma

36.9k55 gold badges1515 silver badges4040 bronze badges

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  ProductClass does not have blank or null values check tableC on the sample code that i posted
  
  – Ronald
  Mar 28 at 12:19
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @Ronald . . Yes tableC don't have a null or blank but first two table have this. So, if you are using this column in GROUP BY it will considered as separate group i.e (blank). So, for that your result would be fine. If you want single group then use max() in ProductClass.
  
  – Yogesh Sharma
  Mar 28 at 12:24
  
  

  
  
  
  

add a comment
 | 

You can use max() for ProductClass if it has value like blank or nulls:

select Invoice, max(ProductClass) as ProductClass, sum(Value) as Value
from(select * from tableA
 union
 select * from tableB
 union
 select * from tableC
 )tableD
group by Invoice;

share|improve this answer

answered Mar 28 at 12:12

Yogesh SharmaYogesh Sharma

36.9k55 gold badges1515 silver badges4040 bronze badges

select Invoice, max(ProductClass) as ProductClass, sum(Value) as Value
from(select * from tableA
 union
 select * from tableB
 union
 select * from tableC
 )tableD
group by Invoice;

share|improve this answer

answered Mar 28 at 12:12

Yogesh SharmaYogesh Sharma

36.9k55 gold badges1515 silver badges4040 bronze badges

answered Mar 28 at 12:12

Yogesh SharmaYogesh Sharma

36.9k55 gold badges1515 silver badges4040 bronze badges

answered Mar 28 at 12:12

Yogesh SharmaYogesh Sharma

36.9k55 gold badges1515 silver badges4040 bronze badges