>>>> 2011/06/17 14:09 +0700, HaidarPesebe >>>> Please help calculate the total revenue from the two databases below. The first database is the name of the item and price. The second database is the goods sold. I will make a recapitulation of every month to my total income (total only). I've tried tp always failed.
TABLE A (item name and price) ------------------------------------------------------------ NO : CODE : NAME : PRICE(USD) : ------------------------------------------------------ 1 : A01 : NAME A : 20 2 : A02 : NAME B : 15 ----------------------------------------------------- TABLE B (items sold) --------------------------------------------------------------------------------- : NO : CODE : CITY : QTY : MONTH : YEAR : ------------------------------------------------------------------------------- : 1 : A01 : PARIS : 20 : 1 : 2011 : : 2 : A01 : LONDON : 11 : 1 : 2011 : : 3 : A02 : PARIS : 15 : 1 : 2011 : : 4 : A02 : PARIS : 10 : 1 : 2011 : : 5 : A01 : PARIS : 7 : 2 : 2011 : : 6 : A01 : LONDON : 8 : 2 : 2011 : : 7 : A02 : LONDON : 10 : 2 : 2011 : -------------------------------------------------------------------------------- the result will be like this NO : DATE (month year) : Total (USD) ----------------------------------------------------------- 1 : 1 - 2011 : 995 2 : 2 - 2011 : 450 Best regards, Haidar <<<<<<<< Clearly, for Table B you want "GROUP BY" (but I suspect you make your life harder by splitting MONTH & YEAR apart. With sql_mode 'ALLOW_INVALID_DATES' set it is all right to enter '2011/2/0' into a variable of timestamp type "DATETIME".), joined with Table A, and further "GROUP BY" on the joined tables. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org