Databases
StackOverflow2013
Postsdbo
POJoining 3 tables on MySQL to sum sales and stock for a product
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJoining 3 tables on MySQL to sum sales and stock for a product
    primarykey
    data
    text
    <p>I have 3 tables:</p> <ol> <li>products (sku, price, priceOffer, etc)</li> <li>stock (sku, branch, items)</li> <li>sales_provider (sku, items, date)</li> </ol> <p>The table products holds all the information about a product, except for stock or sales. Current stock is stored in the table stock, and has information about how many items are available on each branch. The table "sales_provider" stores how many items were sold each day for every product. The product ID is "sku".</p> <p>Now, I'm trying to get with one query the product that:</p> <ol> <li>Has generated the best profit (number of sales * offered price)</li> <li>Is still available on stock</li> </ol> <p>And, of course, I want to know how many items are still on stock and how many items were sold.</p> <p>I'm trying a query like this:</p> <pre><code>select * from ( select p.*, sum(s.items) stock, sum(sp.items) sales, case when p.priceOffer &lt; p.price and p.priceOffer &gt; 0 then p.priceOffer else p.price end finalPrice from products p join stock s on s.sku = p.sku join sales_provider sp on sp.sku = p.sku group by sku ) temp where stock &gt; 0 order by (finalPrice * sales) desc limit 1; </code></pre> <p>But I'm having problems with that. Basically, I'm getting a huge sum of stock items ans sales_provider items, not the real amounts. Also, it's a slow query (it's taking about half a second with only 9,500 products).</p> <p>I've been trying to modify it and I'm having doubts about the subquery being necessary, but I just can't nail it.</p> <p>If someone can help me improve it and get the correct result, I'll really appreciate it.</p> <p>Thanks in advance for any helpful comment.</p> <p>Francisco</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USFrancisco Zarabozo
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POJoining 3 tables on MySQL to sum sales and stock for a product
      1. USRJR
      1. VTFavorite
    1. COYou're looking for the product that brought in the most gross revenue, correct? To compute profit you'd need cost information.
      singulars
      1. POJoining 3 tables on MySQL to sum sales and stock for a product
      1. USO. Jones
    2. CO@OllieJones: Yes, that's correct. The reason I'm not taking the original cost in the query is that all products have a fixed profit of 10%. Whenever there's a `priceOffer`, it's just informative but it's an offer from the provider and it still has the same profit of 10%.
      singulars
      1. POJoining 3 tables on MySQL to sum sales and stock for a product
      1. USFrancisco Zarabozo
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload