On 2012-12-18 2:52 AM, Haidar Pesebe wrote:
> Hi all--
>
>
> There are 3 tables which each like this:
>
> NOTE: The linkage between table A and table B is ID and IDPUBLISHER, while Table
> B to C is the ISBN. Sometimes there are some titles that are not sold
> in a given month.
>
> TABLE A (Publisher)
> -------------------------------
> ID : NAME : EMAIL :
> -------------------------------
> 1 : ABC : abc@abc
> 2 : CDE : cde@cde
> -------------------------------
>
> TABLE B (BOOKS TABLE)
> --------------------------------------------
> : IDBOOK : TITLE : PUBLISHER ID : ISBN
> --------------------------------------------
> : 1 : TITLE 01 : 1 : 001
> : 2 : TITLE 02 : 1 : 002
> : 3 : TITLE 03 : 2 : 003
> : 4 : TITLE 04 : 2 : 004
> --------------------------------------------
>
> TABLE C (SALES OF BOOKS)
>
> ----------------------------------------------
> : IDSALES : ISBN : PRICE : QTY : DATE :
>
> -----------------------------------------------
> : 1 : 001 : 100 : 20 : 2012-12-01 :
> : 2 : 001 : 100 : 11 : 2012-12-01 :
> : 3 : 002 : 60 : 15 : 2012-12-01 :
> : 4 : 003 : 30 : 10 : 2012-12-01 :
> : 5 : 003 : 30 : 7 : 2012-12-01 :
> : 6 : 003 : 30 : 8 : 2012-12-01 :
> : 7 : 004 : 50 : 10 : 2012-12-01 :
> -----------------------------------------------
>
> How do I call up the sales in December 2012 for ABC Publisher or call the
> sale in 2012 for ABC publisher?
select b.idbook, b.title, b.isbn, s.qty, sum(s.qty) as qty, Sum(s.qty *
s.price) as amount
from sales s
join books b on s.isbn = b.isbn
join publisher p on b.publisherID=p.id
group by b.idbook, b.title, b.isbn;
PB
>
>
>
> RESULT OF Sales Books of ABC Publisher in December 2012
>
> ---------------------------------------
>
>
> No. : Books Title : ISBN :QTY : AMOUNT
>
> ---------------------------------------
>
>
> 1. : Title 01 : 001 : 31 : 3,100
>
> 2. : Tile 02 : 002 : 15 : 900
>
>
> .... and so on .........
>
> ---------------------------------------
>
>
> help me to solve this problem
>
> Thanks
>
