SELECT CONCAT(b.month, '-', b.year) AS Date, SUM(b.quantity * a.price) AS Total FROM items AS a, orders AS b WHERE a.code = b.code GROUP BY month, year;
where "items" is "Table A" and "orders" is "Table B" Thanks Aveek On Jun 20, 2011, at 9:27 AM, HaidarPesebe wrote: > Please help us calculate the total amount of revenue each month-year 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 always tried but 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 > > We have tried but does not match the sum qyt. After I check the price turns > out to be called just only the price of A even for multiplication NAME A02 > CODE. > > Over its support I thank you. > > Haidapesebe > > > __________ Information from ESET NOD32 Antivirus, version of virus signature > database 6221 (20110619) __________ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
