List:General Discussion« Previous MessageNext Message »
From:HaidarPesebe Date:November 11 2010 4:22am
Subject:Re: select data from two tables and SUM qty of the same ID
View as plain text  
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" <aveekm@stripped>
To: "HaidarPesebe" <haidarpesebe@stripped>
Cc: "MySQL Lists" <mysql@stripped>
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. 

Thread
select data from two tables and SUM qty of the same IDHaidarPesebe10 Nov
  • Re: select data from two tables and SUM qty of the same IDAveek Misra10 Nov
  • Re: select data from two tables and SUM qty of the same IDHaidarPesebe11 Nov
  • Re: select data from two tables and SUM qty of the same IDHaidarPesebe11 Nov
Re: select data from two tables and SUM qty of the same IDhaidarpesebe10 Nov