select data from two tables and SUM qty of the same ID

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

please help us look for the data select from two tables with the =
following details:
=20
Primary table: product
ID | ProductName | ISBN |
---------------------------
1 | Product A | 123 |
2 | Product B | 456 |
3 | product C | 444 |
---------------------------

second table: salesreport
IDS | ID | CITY | QTY |
------------------------
1 | 1 | New York | 3 |
2 | 1 | Alabama | 5 |
3 | 1 | London | 1 |
4 | 1 | Jakarta | 5 |
5 | 2 | New York | 8 |
6 | 2 | Alabama | 4 |
7 | 2 | London | 9 |
8 | 2 | Jakarta | 3 |
--------------------------

ID in table product same with ID table salesreport
How we will show SALES REPORT product is sold only at the table =
salesreport SUM qty to the top sold and unsold product C will not be =
displayed.

The result will be like this:
--------------------------------
No. Product Name ISBN QTY
--------------------------------
1. product B 456 24
2. product A 123 14
--------------------------------

I'm using PHP. Thanks a lot for information.
------=_NextPart_000_028F_01CB80FB.F4BB3FD0--

Re: select data from two tables and SUM qty of the same ID

SELECT product.ID, product.ProductName, product.ISBN, =
SUM(salesreport.QTY) as Total_Quantity FROM salesreport, product WHERE =
product.ID =3D salesreport.ID GROUP BY salesreport.ID ORDER BY =
Total_Quantity DESC;



On Nov 10, 2010, at 3:53 PM, HaidarPesebe wrote:

> please help us look for the data select from two tables with the =
following details:
>=20
> Primary table: product
> ID | ProductName | ISBN |
> ---------------------------
> 1 | Product A | 123 |
> 2 | Product B | 456 |
> 3 | product C | 444 |
> ---------------------------
>=20
> second table: salesreport
> IDS | ID | CITY | QTY |
> ------------------------
> 1 | 1 | New York | 3 |
> 2 | 1 | Alabama | 5 |
> 3 | 1 | London | 1 |
> 4 | 1 | Jakarta | 5 |
> 5 | 2 | New York | 8 |
> 6 | 2 | Alabama | 4 |
> 7 | 2 | London | 9 |
> 8 | 2 | Jakarta | 3 |
> --------------------------
>=20
> ID in table product same with ID table salesreport
> How we will show SALES REPORT product is sold only at the table =
salesreport SUM qty to the top sold and unsold product C will not be =
displayed.
>=20
> The result will be like this:
> --------------------------------
> No. Product Name ISBN QTY
> --------------------------------
> 1. product B 456 24
> 2. product A 123 14
> --------------------------------
>=20
> I'm using PHP. Thanks a lot for information.


--
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: select data from two tables and SUM qty of the same ID

VGhhbmtzIGEgbG90LiBXZSB3aWxsIHRyeSBhbmQgaW5mb3JtIHlvdS4NCg0K LS0tLS0tT3JpZ2lu
YWwgTWVzc2FnZS0tLS0tLQ0KRnJvbTogQXZlZWsgTWlzcmENClRvOiBBbGJh aGFyIFV5YQ0KQ2M6
IE15U1FMIExpc3RzDQpTdWJqZWN0OiBSZTogc2VsZWN0IGRhdGEgZnJvbSB0 d28gdGFibGVzIGFu
ZCBTVU0gcXR5IG9mIHRoZSBzYW1lIElEDQpTZW50OiBOb3YgMTAsIDIwMTAg MTc6MzgNCg0KU0VM
RUNUIHByb2R1Y3QuSUQsIHByb2R1Y3QuUHJvZHVjdE5hbWUsIHByb2R1Y3Qu SVNCTiwgU1VNKHNh
bGVzcmVwb3J0LlFUWSkgYXMgVG90YWxfUXVhbnRpdHkgRlJPTSBzYWxlc3Jl cG9ydCwgcHJvZHVj
dCBXSEVSRSBwcm9kdWN0LklEID0gc2FsZXNyZXBvcnQuSUQgR1JPVVAgQlkg c2FsZXNyZXBvcnQu
SUQgT1JERVIgQlkgVG90YWxfUXVhbnRpdHkgREVTQzsNCg0KDQoNCk9uIE5v diAxMCwgMjAxMCwg
YXQgMzo1MyBQTSwgSGFpZGFyUGVzZWJlIHdyb3RlOg0KDQo+IHBsZWFzZSBo ZWxwIHVzIGxvb2sg
Zm9yIHRoZSBkYXRhIHNlbGVjdCBmcm9tIHR3byB0YWJsZXMgd2l0aCB0aGUg Zm9sbG93aW5nIGRl
dGFpbHM6DQo+IA0KPiBQcmltYXJ5IHRhYmxlOiBwcm9kdWN0DQo+IElEIHwg UHJvZHVjdE5hbWUg
fCBJU0JOIHwNCj4gLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tDQo+IDEg fCBQcm9kdWN0IEEg
ICAgICB8IDEyMyB8DQo+IDIgfCBQcm9kdWN0IEIgICAgICB8IDQ1NiB8DQo+ IDMgfCBwcm9kdWN0
IEMgICAgICB8IDQ0NCB8DQo+IC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0t LQ0KPiANCj4gc2Vj
b25kIHRhYmxlOiBzYWxlc3JlcG9ydA0KPiBJRFMgfCBJRCB8IENJVFkgICAg ICB8IFFUWSB8DQo+
IC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQ0KPiAxICAgIHwgMSB8IE5ldyBZ b3JrIHwgMyB8DQo+
IDIgICAgfCAxIHwgQWxhYmFtYSAgIHwgNSB8DQo+IDMgICAgfCAxIHwgTG9u ZG9uICAgIHwgMSB8
DQo+IDQgICAgfCAxIHwgSmFrYXJ0YSAgICB8IDUgfA0KPiA1ICAgIHwgMiB8 IE5ldyBZb3JrIHwg
OCB8DQo+IDYgICAgfCAyIHwgQWxhYmFtYSAgIHwgNCB8DQo+IDcgICAgfCAy IHwgTG9uZG9uICAg
IHwgOSB8DQo+IDggICAgfCAyIHwgSmFrYXJ0YSAgICB8IDMgfA0KPiAtLS0t LS0tLS0tLS0tLS0t
LS0tLS0tLS0tLQ0KPiANCj4gSUQgaW4gdGFibGUgcHJvZHVjdCBzYW1lIHdp dGggSUQgdGFibGUg
c2FsZXNyZXBvcnQNCj4gSG93IHdlIHdpbGwgc2hvdyBTQUxFUyBSRVBPUlQg cHJvZHVjdCBpcyBz
b2xkIG9ubHkgYXQgdGhlIHRhYmxlIHNhbGVzcmVwb3J0IFNVTSBxdHkgdG8g dGhlIHRvcCBzb2xk
IGFuZCB1bnNvbGQgcHJvZHVjdCBDIHdpbGwgbm90IGJlIGRpc3BsYXllZC4N Cj4gDQo+IFRoZSBy
ZXN1bHQgd2lsbCBiZSBsaWtlIHRoaXM6DQo+IC0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0t
LS0tDQo+IE5vLiBQcm9kdWN0IE5hbWUgSVNCTiBRVFkNCj4gLS0tLS0tLS0t LS0tLS0tLS0tLS0t
LS0tLS0tLS0tLS0NCj4gMS4gICBwcm9kdWN0IEIgICAgICA0NTYgICAgMjQN Cj4gMi4gICBwcm9k
dWN0IEEgICAgICAxMjMgICAgMTQNCj4gLS0tLS0tLS0tLS0tLS0tLS0tLS0t LS0tLS0tLS0tLS0N
Cj4gDQo+IEknbSB1c2luZyBQSFAuIFRoYW5rcyBhIGxvdCBmb3IgaW5mb3Jt YXRpb24uDQoNCg0K
DQpTZW50IGZyb20gbXkgQmxhY2tCZXJyea4=

Re: select data from two tables and SUM qty of the same ID

we've tried to use as your dialing purposes, but still can not.

This structure

SELECT product.product_id, product.isbn, product.name, product.author,
product.harga, product.discount, sum(sales_report.qty) as totalqty FROM " .
$prefix . "_sales_report, " . $prefix . "_product WHERE product.product_id =
sales_report.bid GROUP BY sales_report.bid ORDER BY totalqty DESC

Display error on the screen like this:

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result
resource in E:\xampp\htdocs\test\inc\sqllayer.php on line 271
Unknown column 'product.product_id' in 'field list'

Note: product_id (product) = bid (sales_report)

haidarpesebe

----- Original Message -----
From: "Aveek Misra"
To: "HaidarPesebe"
Cc: "MySQL Lists"
Sent: Wednesday, November 10, 2010 5:38 PM
Subject: Re: select data from two tables and SUM qty of the same ID


SELECT product.ID, product.ProductName, product.ISBN, SUM(salesreport.QTY)
as Total_Quantity FROM salesreport, product WHERE product.ID =
salesreport.ID GROUP BY salesreport.ID ORDER BY Total_Quantity DESC;



On Nov 10, 2010, at 3:53 PM, HaidarPesebe wrote:

> please help us look for the data select from two tables with the following
> details:
>
> Primary table: product
> ID | ProductName | ISBN |
> ---------------------------
> 1 | Product A | 123 |
> 2 | Product B | 456 |
> 3 | product C | 444 |
> ---------------------------
>
> second table: salesreport
> IDS | ID | CITY | QTY |
> ------------------------
> 1 | 1 | New York | 3 |
> 2 | 1 | Alabama | 5 |
> 3 | 1 | London | 1 |
> 4 | 1 | Jakarta | 5 |
> 5 | 2 | New York | 8 |
> 6 | 2 | Alabama | 4 |
> 7 | 2 | London | 9 |
> 8 | 2 | Jakarta | 3 |
> --------------------------
>
> ID in table product same with ID table salesreport
> How we will show SALES REPORT product is sold only at the table
> salesreport SUM qty to the top sold and unsold product C will not be
> displayed.
>
> The result will be like this:
> --------------------------------
> No. Product Name ISBN QTY
> --------------------------------
> 1. product B 456 24
> 2. product A 123 14
> --------------------------------
>
> I'm using PHP. Thanks a lot for information.


__________ Information from ESET Smart Security, version of virus signature
database 5608 (20101110) __________

The message was checked by ESET Smart Security.

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

Re: select data from two tables and SUM qty of the same ID

I tried this and succeeded. Thank you

SELECT product_id, isbn, name, author, harga, discount, sum(qty) as totalqty
FROM " . $prefix . "_sales_report b INNER JOIN " . $prefix . "_product p ON
(p.product_id = b.bid) GROUP BY b.bid ORDER BY totalqty DESC
haidarpesebe
----- Original Message -----
From: "Aveek Misra"
To: "HaidarPesebe"
Cc: "MySQL Lists"
Sent: Wednesday, November 10, 2010 5:38 PM
Subject: Re: select data from two tables and SUM qty of the same ID


SELECT product.ID, product.ProductName, product.ISBN, SUM(salesreport.QTY)
as Total_Quantity FROM salesreport, product WHERE product.ID =
salesreport.ID GROUP BY salesreport.ID ORDER BY Total_Quantity DESC;



On Nov 10, 2010, at 3:53 PM, HaidarPesebe wrote:

> please help us look for the data select from two tables with the following
> details:
>
> Primary table: product
> ID | ProductName | ISBN |
> ---------------------------
> 1 | Product A | 123 |
> 2 | Product B | 456 |
> 3 | product C | 444 |
> ---------------------------
>
> second table: salesreport
> IDS | ID | CITY | QTY |
> ------------------------
> 1 | 1 | New York | 3 |
> 2 | 1 | Alabama | 5 |
> 3 | 1 | London | 1 |
> 4 | 1 | Jakarta | 5 |
> 5 | 2 | New York | 8 |
> 6 | 2 | Alabama | 4 |
> 7 | 2 | London | 9 |
> 8 | 2 | Jakarta | 3 |
> --------------------------
>
> ID in table product same with ID table salesreport
> How we will show SALES REPORT product is sold only at the table
> salesreport SUM qty to the top sold and unsold product C will not be
> displayed.
>
> The result will be like this:
> --------------------------------
> No. Product Name ISBN QTY
> --------------------------------
> 1. product B 456 24
> 2. product A 123 14
> --------------------------------
>
> I'm using PHP. Thanks a lot for information.


--
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