From: Peter Brawley
Date: December 18 2012 7:29pm
Subject: Re: sales data every publisher
List-Archive: http://lists.mysql.com/mysql/228829
Message-Id: <50D0C428.9050007@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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
>