All geek questions in one place

Sum of multiple MYSQL tables using a primary key

I still like MySQL very much, so I find it difficult to find an answer to this specific problem.

I have approximately 60 supplier tables that I would like to combine into one table. Many of our sellers carry the same elements, so I would like to add all the elements to the same table, and those that have the same sku, add the quantity together. I would also like to add a lot of value and price, and round it down to 0.99, but if it gets too complicated or can't be sleepy, for now I will only stick to sku and quanitity. I would like to start with 2 tables until I can figure it out, and as soon as I understand the solution, I can work on all of them. Here is an example from two tables:

Table A (SKU will be the primary key) SKU Vendor A SKU Cost Price QTY 1000 VA100 49.99 62.99 10 1001 VA101 64.44 81.99 6 1002 VA102 37.67 47.99 24 1003 VA103 28.33 35.99 16 1004 VA104 26.33 35.99 18 Table B (SKU will be the primary key) SKU Vendor B SKU Cost Price QTY 1000 VB100 49.99 62.95 21 1001 VB101 64.44 81.95 16 5001 VB501 32.67 40.95 4 5002 VB502 88.44 110.95 12 5003 VB503 87.21 109.95 2

So, the completion result will look like this:

 SKU Vendor A SKU Vendor B SKU Cost Price QTY 1000 VA100 VB100 49.99 62.99 31 <-- Sum 1001 VA101 VB101 64.44 81.99 22 <-- Sum 1002 VA102 37.67 47.99 24 1003 VA103 28.33 35.99 16 1004 VA104 26.33 35.99 18 5001 VB501 32.67 40.95 4 5002 VB502 88.44 110.95 12 5003 VB503 87.21 109.95 2

As you can see, the first 2 had added quantities, as well as great value and price. I tried the options:

 Create Table MASTER SELECT TableA.sku,TableA.VendorA_sku,TableA.cost,TableA.price,TableA.qty UNION ALL SELECT TableB.sku,TableB.VendorB_sku,TableB.cost,TableB.price,TableB.qty GROUP BY sku

And options:

 Create Table MASTER AS SELECT TableA.sku,TableA.VendorA_sku,TableA.cost,TableA.price,SUM(TableA.qty+TableB.qty) GROUP BY sku

I tried many different types of Joins and union and it seems that I could not get anything to see how I need it, and I did not play with adding and rounding the price and price, t finish adding quanitity in double primary keys. I have to do this daily, using approximately 60 tables and about 600 thousand Sku, so any help would be greatly appreciated.

( I also apologize in advance if I did not type it correctly, as I am new to these types of forums )

+4
source share
1 answer

The following should work for you:

 select sku, min(case when Type = 'VendorA' THEN Vendor END) VendorA_SKU, min(case when Type = 'VendorB' THEN Vendor END) VendorB_SKU, cost, price, sum(QTY) Total from ( select sku, VendorA_SKU Vendor, cost, price, qty, 'VendorA' Type from tablea union all select sku, VendorB_SKU, cost, price, qty, 'VendorB' Type from tableb ) x group by sku

see SQL Fiddle with Demo

A few things to decide if you want AVG() or MAX() price and price if the values ​​are different or which one you want to return? If you do this, you will use the following to return MAX() :

 select sku, min(case when Type = 'VendorA' THEN Vendor END) VendorA_SKU, min(case when Type = 'VendorB' THEN Vendor END) VendorB_SKU, max(cost) MaxCost, max(price) MaxPrice, sum(QTY) Total from ( select sku, VendorA_SKU Vendor, cost, price, qty, 'VendorA' Type from tablea union all select sku, VendorB_SKU, cost, price, qty, 'VendorB' Type from tableb ) x group by sku

see SQL Fiddle with Demo

+2
source

Source: https://habr.com/ru/post/1435116/

More articles:


All Articles