How to query a table in the database and copy it's data into one one?

Question

I am so lost in this situation and badly need a help. The case is this. I copy a data value from a table, let say table book_book So i use:

INSERT INTO book_copy(name,qty,price,year,isbn)
    SELECT bb.name,bb.qty,bb.price,bb.total,bb.year
    FROM book_book bb WHERE ss.name = 'sample PR' and ss.isbn = 'PH019BN'

what the output showed me was:

name       |   qty      |    price    | year     |  isbn
sample PR  |    4       |             |          |  PH019BN
sample PR  |            |     20.00   |          |  PH019BN
sample PR  |            |             |    1991  |  PH019BN

but i what i want is to view it as:

name       |   qty      |    price    | year     |  isbn
sample PR  |   4        |     20.00   | 1991     |  PH019BN

Any help is much appreciated...

Answer 1

What you're looking for is called "pivoting". From your example data, it looks like name and isbn are always filled in, and qty, price, and year in one row only. You can pivot that like:

SELECT name, max(qty), min(price), avg(year), isbn
FROM book_book bb 
WHERE name = 'sample PR' and isbn = 'PH019BN'
GROUP BY name, isbn

Answer 2

You cannot do SUM on rows which has NULL values. because the result will be a NULL, anything added to NULL will result in NULL.

Example : select sum(10+NULL+NULL) from dual will result in NULL.

you can try the below , if there are multiple rows with qty column being NOT NULL it works by adding them all:

SELECT bb.name, 
(SELECT SUM(qty) FROM BOOK_BOOK s WHERE s.name = bb.name 
AND s.isbn = bb.isbn and s.qty IS NOT NULL) as QTY ,
(SELECT SUM(price) FROM BOOK_BOOK p WHERE p.name = bb.name 
AND p.isbn = bb.isbn and p.price IS NOT NULL) as price,
(SELECT MAX(year) FROM BOOK_BOOK y WHERE y.name = bb.name 
AND y.isbn = bb.isbn and y.year IS NOT NULL) as year
FROM BOOK_BOOK bb WHERE bb.name = 'sample PR' AND bb.isbn = 'PH019BN'