On 12/18/2012 3: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?
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
1) You need some joins. This is how you link your rows together. For
data that can be there but isn't required to be there, you use one of
the OUTER JOIN terms of LEFT JOIN or RIGHT JOIN. This allows us to
combine columns from different tables into the same report. This is also
a good time to learn about using aliases for table names and column names
2) You need a GROUP BY to summarize certain values (like amount) for the
rows you retrieve.
3) You use some conditions in a WHERE clause to limit what it is you
want to summarize.
SELECT
p.name # the name of the publisher
, b.title as 'Books Title'
, b.ISBN
, SUM(s.QTY) as 'QTY' # the number actual books sold
, SUM(s.QTY * s.PRICE) as 'AMOUNT' # the total value of all books sold
FROM publishers p
INNER JOIN books b
ON b.`publisher id` = p.id
# this is optional information as a book may not have any sales data for
the given date range so we use a LEFT JOIN
LEFT JOIN sales s
ON s.ISBN = b.ISBN
WHERE s.date >= '2012-12-01' and s.date < '2013-01-01'
GROUP BY p.name, b.title, b.ISBN
For more details:
http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html
http://dev.mysql.com/doc/refman/5.5/en/examples.html
http://dev.mysql.com/doc/refman/5.5/en/select.html
http://dev.mysql.com/doc/refman/5.5/en/join.html
And, as always, you can ask the list.
Best wishes,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
