How to sum two columns of different tables in sql

Question

I created two tables book and cd

**Books** **Book_id** **author** **publisher** **rate** angular2 132 venkat ts 1900 angular 160 venkat ts 1500 html 5 165 henry vk 1500 html 231 henry vk 2500 css 256 mark adobe 1600 java 352 john gulberg 4500 c# 450 henry adobe 1600 jsp 451 henry vk 2500 ext js 555 kv venkat w3 5102 html 560 kv venkat gulberg 5000 java2 561 john gulberg 9500 java8 651 henry vk 1650 js 654 henry ts 2500 java 777 babbage adobe 5200 phython 842 john ts 1500 spring 852 henry w3 6230 spring 895 mark tut 4250 ext js 965 henry gulberg 4500

book_id              Cd_name           Cd_price
132                  angular2          500
132                  angular1          600
132                  angular basics    600
132                  angular expert    900
160                  begineer_course   1200
160                  angular_templates 500
165                  html_tutorials    900
165                  bootstrap         1000
256                  css styles        650
256                  expert css        900
555                  extjs             1200
555                  exjs_applications 500
777                  core java         2500
777                  java swing        4500
777                  java tutorials    1500
842                  phython           650
852                  spring            900
852                  spring mvc        900

In the above two tables i want to join the books,author,cd_name and the total cost of book and cd for each id.

Expected Output

Books       Book_id     author  cd_name       total price
angular2    132         venkat  angular2         2400
angular2    132         venkat  angular basics   2100
angular2    132         venkat  angular expert   2800
java        777         babbage core java        7700

Like the above result i need to get the total cost for all the books and cd

I'd suggest to rather use the simple "rate + cd_price AS total_price" instead of any SUM function. In order to do so, simply join the two tables. — Tyron78
– Tyron78, Commented Sep 12, 2017 at 7:44
select books,author,cd_name,rate,cd_price from book join cd on book.book_id = cd.book_id where (book.rate + cd.cd_price) as total_price — Jerold Joel
– Jerold Joel, Commented Sep 12, 2017 at 8:06

etsa · Accepted Answer · 2017-09-12 09:08:36Z

2

In case not all books had a CD:

SELECT A.Books
       , A.Book_ID
       , A.Author
       , B.CD_Name
       , A.rate+COALESCE(B.Cd_price,0) AS TOTAL_PRICE
FROM BOOK A
LEFT JOIN CD B ON A.BOOK_ID = B.BOOK_ID

Question's author evidenced that "The table name is book not Books"

I used originally BOOKS as a "suggestion" because (usually) tables name are plural.

edited Sep 12, 2017 at 9:08

answered Sep 12, 2017 at 8:19

etsa

5,0501 gold badge9 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jerold Joel Over a year ago

Why this code is not working ------- ------- select books,author,cd_name,rate,cd_price from book join cd on book.book_id = cd.book_id where (book.rate + cd.cd_price) as total_price

etsa Over a year ago

The syntax is wrong: you can't write "WHERE (book.rate + cd.cd_price) as total_price". You can't use AS in WHERE. Instead you could write, for example, "WHERE (book.rate + cd.cd_price) > 100" (P.S. Bracket can be eliminated)

Yeou · Accepted Answer · 2017-09-12 07:53:12Z

2

Try this

select books, b.book_id, author, cd_name, (b.rate+c.cd_price) as total_price from book b
join cd c on b.book_id = c.book_id

answered Sep 12, 2017 at 7:53

Yeou

6321 gold badge7 silver badges21 bronze badges

2 Comments

doc pinocchio Over a year ago

i understood the syntax but i am new to this and coundnt understand the purpose of "b.book_id" ( why do we need b. ) and using the same " b. " again as b.rate , and again somthing like this in the table name " join cd c " (what does that extra ' c ' do)

Yeou Over a year ago

@docpinocchio Sorry for the late reply. When joining tables you can give an alias(short name) to the table, and use this alias to tell the SQL that we want to use the column from the specific table. The main purpose of using this is to remove ambiguity as SQL cannot decide on which table column to use if both tables have a column with the same name. You can choose not to use an alias for other columns. But it is recommended for clarity.

Collectives™ on Stack Overflow

How to sum two columns of different tables in sql

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related