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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql