List: General Discussion « Previous MessageNext Message » From: HaidarPesebe Date: June 20 2011 4:53am Subject: Re: calculate the total amount of revenue each month-year View as plain text
```I've tried it and succeeded. Thanks for the help Mr. Misra.

Best regards,
Haidarpesebe

.
----- Original Message -----
From: "Aveek Misra" <aveekm@stripped>
To: "HaidarPesebe" <haidarpesebe@stripped>
Cc: "MySQL Lists" <mysql@stripped>
Sent: Monday, June 20, 2011 11:28 AM
Subject: Re: calculate the total amount of revenue each month-year

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
>

__________ Information from ESET NOD32 Antivirus, version of virus signature
database 6221 (20110619) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

__________ Information from ESET NOD32 Antivirus, version of virus signature database 6221
(20110619) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com

```