I have 2 tables Booked
+-----------+------------+---------+------------+ | ID_Booked | ID_Member | ID_Book | Date | +-----------+------------+---------+------------+ | 1 | 1 | 4 | 2020-07-10 | | 2 | 3 | 1 | 2020-05-09 | | 3 | 1 | 3 | 2020-06-04 | | 4 | 2 | 2 | 2020-07-09 | | 5 | 4 | 1 | 2009-07-09 | +-----------+------------+---------+------------+
Books
+---------+-----------------------------+-----------+-------+---------------+ | ID_Book | Tittle | Genre | Price | Author | +---------+-----------------------------+-----------+-------+---------------+ | 1 | Mengejar Mimpi | Novel | 24000 | Aries M.Z. | | 2 | Sistem Basis Data | Tech | 32000 | Fathansyah | | 3 | Global Business Today Bed | Business | 40000 | Mc. Graw Hill | | 4 | Object Oriented Programming | Tech | 35000 | Rinaldi Munir | | 5 | Algoritma dan Pemrograman | Tech | 65000 | Rinaldi Munir | +---------+-----------------------------+-----------+-------+---------------+
I’m currently working on this query but the result is not correct.
SELECT B.*, MIN(Total) FROM (SELECT COUNT(*) AS Total FROM booked GROUP BY ID_Buku) AS Results, books B
Output
+---------+----------------+-------+-------+------------+------------+ | ID_Buku | NM_Buku | Jenis | Harga | Pengarang | MIN(Total) | +---------+----------------+-------+-------+------------+------------+ | 1 | Mengejar Mimpi | Novel | 24000 | Aries M.Z. | 1 | +---------+----------------+-------+-------+------------+------------+
The result that i want is
+---------+-----------------------------+-------+-------+------------+------------+ | ID_Buku | NM_Buku | Jenis | Harga | Pengarang | MIN(Total) | +---------+-----------------------------+-------+-------+------------+------------+ | 2 | Sistem Basis Data | Novel | 24000 | Aries M.Z. | 1 | | 3 | Global Business Today Bed | Novel | 24000 | Aries M.Z. | 1 | | 4 | Object Oriented Programming | Novel | 24000 | Aries M.Z. | 1 | +---------+-----------------------------+-------+-------+------------+------------+
Can you help me to fix it? Thanks. Sorry for my bad english
Advertisement
Answer
Your question lacks the clarity but to my instinct of what you have posted may be this will work, as far as i can understand you might want to show the min price on all the records except the min priced record and count the number of the same records, if this is the case, you can try this.
select b.Id_book as ID_Buku , b.tittle as NM_Buku , b.genre as Jenis , (select min(price) from books) as Harga , (select Author from books where price= (select min(price) from books )) as Pengarang,count(b.Id_book) from booked a, books b where a.ID_Book = b.ID_Book and b.price != (select min(price) from books) group by b.Id_book , b.tittle , b.genre , (select min(price) from books) , (select Author from books where price= (select min(price) from books )) order by 1;
The insert scripts i used to match the data you have posted.
create table Booked ( ID_Booked integer, ID_Member integer, ID_Book integer, Date date ); create table Books ( ID_Book integer, Tittle varchar(300), Genre varchar(100), Price integer, Author varchar(100) ); insert into Booked values ( 1 , 1 , 4 , '2020-07-10' ), ( 2 , 3 , 1 , '2020-05-09' ), ( 3 , 1 , 3 , '2020-06-04' ) ,( 4 , 2 , 2 , '2020-07-09' ) ,( 5 , 4 , 1 , '2009-07-09'); insert into books values ( 1 , 'Mengejar Mimpi' , 'Novel' , 24000 , 'Aries M.Z.' ), ( 2 , 'Sistem Basis Data' , 'Tech' , 32000 , 'Fathansyah' ), ( 3 , 'Global Business Today Bed' , 'Business' , 40000 , 'Mc. Graw Hill' ), ( 4 , 'Object Oriented Programming' , 'Tech' , 35000 , 'Rinaldi Munir' ), ( 5 , 'Algoritma dan Pemrograman' , 'Tech' , 65000 , 'Rinaldi Munir' );