CODEWITHSUNDEEP
mysqlsqlcountsummultiple-tables
John Hall
John Hall

Reputation: 1346

Multiple Table Query with SUM and COUNT

I am struggling writing a (to me!) complex query which will calculate the total number of units sold of items, pulling data from two separate tables. I am using MySQL.

The first table is a simple 'order_contents' table (sale_ord_contents in my example) with item_id's and the quantity sold. Some items may be an Assembly, in which case their contents are stored in another table, sale_ord_assembly_contents, along with the quantity of each subcomponent. In my example here, the only 'Assembly' is the Fruit Basket. I have made a note that one of the Fruit Basket entries has a quantity sold of (2), meaning all of its contents should have their quantity multiplied by two.

enter image description here

My goal is to find the 20 most common items by their accumulative quantity sold between the two tables. The part I am struggling with the most is how to handle multiplying the values of subcomponents by their quantity of the parent in the sale_ord_contents table.

I have written this query which handles the sale_ord_contents calculation, but I am having trouble joining in the data from the sale_ord_assembly_contents table...

SELECT
   item_id, name, sku,
   SUM(quantity) AS purchases
FROM
   sale_ord_contents
GROUP BY
   item_id
ORDER BY purchases DESC
LIMIT 20

Am I going to be better off doing this in PHP, or is this feasible in MySQL? If so, could a SQL guru assist me in writing a query that provides the desired results? Thank you!

Upvotes: 0

Views: 1222

Answers (3)

Jenn
Jenn

Reputation: 795

The key to solving your problem is a left join. Essentially what this does is it matches up values between the table on the left and the table on the right side of the left join. If the table on the right doesn't have any values that match up correctly, null is inserted instead. Doing a left join between sale_ord_contents and sale_ord_assembly_contents will yield results like this:

Sale_ord_contents_name | quantity | sale_ord_assembly_contents_name | quantity
apple                  | 5        |    null                         |  null
fruitbasket 1          | 1        |    apple                        |  3
fruitbasket 1          | 1        |    orange                       |  1

These numbers don't match up with the example you provided, but you get the point. This is exactly what you will need to find the correct numbers.

The next keyword that is important is coalesce. What this does is it goes through the list of items that you have provided and returns the first value that is not null. So if I use coalesce(sale_ord_assembly_contents_name, sale_ord_contents_name), for the above example the null in the assembly table will be ignored and apple will be returned. Then, since the assembly name exists for the next two rows, apple and orange will be the value returned by the coalesce.

I believe the following query is what you will need:

SELECT
   coalesce(soa.item_id, so.item_id) as item_id,
   coalesce(soa.name, so.name) as name,
   so.sku,
   SUM(so.quantity * coalesce(soa.quantity, 1)) AS purchases
FROM
   sale_ord_contents so
   left join sale_ord_assembly_contents soa
   on so.id = soa.order_item_id
GROUP BY
   item_id, name, so.sku
ORDER BY purchases DESC
LIMIT 20

Upvotes: 0

monitor
monitor

Reputation: 261

Updated my answer, please try again the following:

SELECT
    IF (soac.item_id IS NULL, soc.item_id, soac.item_id) as item_id,
    IF (soac.name IS NULL, soc.name, soac.name) as name,
    SUM(soc.quantity * COALESCE(soac.quantity, 1)) AS total_sold
FROM
    sale_ord_contents soc
        LEFT OUTER JOIN sale_ord_assembly_contents soac
           ON soac.order_item_id = soc.id
GROUP BY
    IF (soac.quantity IS NULL, soc.item_id, soac.item_id),
    IF (soac.name IS NULL, soc.name, soac.name)
ORDER BY total_sold DESC LIMIT 20

Upvotes: 1

user3844830
user3844830

Reputation: 44

Yes its feasible and the query execution time is also less as you have limit the number of rows returned. Please check in query optimizer in mysql workbench to get the query response time :). Writing too many for loops in php will degrade the page response time and exection time will be more. So its better to write high level application queries in your php code to get the better response time for your web pages :)

Upvotes: 0

Related Questions