calculate the total amount of revenue each month-year

------=_NextPart_000_0139_01CC2F38.CF4D5E90
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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.=20

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 :=20
: 2 : A01 : LONDON : 11 : 1 : 2011 :=20
: 3 : A02 : PARIS : 15 : 1 : 2011 :=20
: 4 : A02 : PARIS : 10 : 1 : 2011 :=20
: 5 : A01 : PARIS : 7 : 2 : 2011 :=20
: 6 : A01 : LONDON : 8 : 2 : 2011 :=20
: 7 : A02 : LONDON : 10 : 2 : 2011 :=20
------------------------------------------------------------ -------------=
-------

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.=20

Over its support I thank you.

Haidapesebe


__________ Information from ESET NOD32 Antivirus, version of virus signatur=
e database 6221 (20110619) __________

The message was checked by ESET NOD32 Antivirus.

http://www.eset.com


------=_NextPart_000_0139_01CC2F38.CF4D5E90--

Re: calculate the total amount of revenue each month-year

SELECT CONCAT(b.month, '-', b.year) AS Date, SUM(b.quantity * a.price) AS T=
otal FROM items AS a, orders AS b WHERE a.code =3D b.code GROUP BY month, y=
ear;

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 pr=
ice. 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.=
=20
>=20
> TABLE A (item name and price)
> ------------------------------------------------------------
> NO : CODE : NAME : PRICE(USD) :
> ------------------------------------------------------
> 1 : A01 : NAME A : 20
> 2 : A02 : NAME B : 15
> -----------------------------------------------------
>=20
> TABLE B (items sold)
> ------------------------------------------------------------ -------------=
--------
> : NO : CODE : CITY : QTY : MONTH : YEAR :
> ------------------------------------------------------------ -------------=
------
> : 1 : A01 : PARIS : 20 : 1 : 2011 :=20
> : 2 : A01 : LONDON : 11 : 1 : 2011 :=20
> : 3 : A02 : PARIS : 15 : 1 : 2011 :=20
> : 4 : A02 : PARIS : 10 : 1 : 2011 :=20
> : 5 : A01 : PARIS : 7 : 2 : 2011 :=20
> : 6 : A01 : LONDON : 8 : 2 : 2011 :=20
> : 7 : A02 : LONDON : 10 : 2 : 2011 :=20
> ------------------------------------------------------------ -------------=
-------
>=20
> the result will be like this
>=20
> NO : DATE (month year) : Total (USD)
> -----------------------------------------------------------
> 1 : 1 - 2011 : 995
> 2 : 2 - 2011 : 450
>=20
> We have tried but does not match the sum qyt. After I check the price tur=
ns out to be called just only the price of A even for multiplication NAME A=
02 CODE.=20
>=20
> Over its support I thank you.
>=20
> Haidapesebe
>=20
>=20
> __________ Information from ESET NOD32 Antivirus, version of virus signat=
ure database 6221 (20110619) __________
>=20
> The message was checked by ESET NOD32 Antivirus.
>=20
> http://www.eset.com
>=20


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: calculate the total amount of revenue each month-year

I've tried it and succeeded. Thanks for the help Mr. Misra.

Best regards,
Haidarpesebe

..
----- Original Message -----
From: "Aveek Misra"
To: "HaidarPesebe"
Cc: "MySQL Lists"
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




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org