List:General Discussion« Previous MessageNext Message »
From:Shawn Green Date:December 18 2012 7:43pm
Subject:Re: sales data every publisher
View as plain text  
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


Thread
sales data every publisherHaidar Pesebe18 Dec
  • Re: sales data every publisherPeter Brawley18 Dec
    • Re: sales data every publisherHaidar Pesebe19 Dec
  • Re: sales data every publisherShawn Green18 Dec