CODEWITHSUNDEEP
mysqlsql
da1lbi3
da1lbi3

Reputation: 4519

Join tables and preform aggregation on each of them

I have the following tables:

table part_list:

part_number | description | type
100           blablabla     blabla

table part_list_supplier:

part_id | artikel
100       100100
100       200100

and I have this query:

select part_list.part_number, part_list.description, part_list.type, group_concat(coalesce(part_list_supplier.artikel, "nothing")) as "artikel"
from part_list 
left join part_list_supplier on (part_list.part_number = part_list_supplier.part_id)
group by part_list.part_number;

this is the result:

part_number | description | type   | artikel
100           blablablabla  blabla   100100,200100

but I want to show the total stock per partnumber behind it. table receipt:

Number |  import 
100     5   
100     10

table sales:

Number | sold 
100     5

this is my query for one table:

SELECT SUM(sold) AS sold
 FROM sales WHERE number = '".$partnumber.”'

but I want to calculate the stock per number and that must be shown behind the other results. the full result: 

part_number | description | type   | artikel            | stock
100           blablablabla  blabla   100100,200100        10

The stock should be 10 because the total number of imports is 15 (5 + 10) and the total number of sales is 5.

Upvotes: 2

Views: 42

Answers (2)

AdamMc331
AdamMc331

Reputation: 16691

I broke this up into pieces to solve it. I started by writing two queries, one that counted total receipt and one that counted total sales:

SELECT r.number, SUM(r.import) AS totalIn
FROM receipt r
GROUP BY r.number;

SELECT s.number, SUM(s.sold) AS totalOut
FROM sales s
GROUP BY s.number;

Then, I used those as two subqueries of a join to get the stock:

SELECT r.number, totalIn - totalOut AS stock
FROM(
  SELECT r.number, SUM(r.import) AS totalIn
  FROM receipt r
  GROUP BY r.number) r
JOIN(
  SELECT s.number, SUM(s.sold) AS totalOut
  FROM sales s
  GROUP BY s.number) s ON s.number = r.number;

Once I verfied this gave the proper stock, I was able to include those subqueries into your original query to build this:

SELECT pl.part_number, pl.description, pl.type, 
    GROUP_CONCAT(COALESCE(pls.artikel, "Nothing.")) AS artikel,
    r.totalIn - s.totalOut AS stock
FROM part_list pl
LEFT JOIN part_list_supplier pls ON pls.part_id = pl.part_number
JOIN(
  SELECT number, SUM(import) AS totalIn
  FROM receipt
  GROUP BY number) r ON r.number = pl.part_number
JOIN(
  SELECT number, SUM(sold) AS totalOut
  FROM sales
  GROUP BY number) s ON s.number = r.number
GROUP BY pl.part_number;

Here is an SQL Fiddle example.

Upvotes: 1

linesarefuzzy
linesarefuzzy

Reputation: 2000

I may not be understanding your question properly, but can't you just add sum(sales.sold) to your select statement and join the sales table? E.g.:

select part_list.part_number, part_list.description, part_list.type, group_concat(coalesce(part_list_supplier.artikel, "nothing")) as "artikel", sum(sales.sold)
from part_list 
left join part_list_supplier on (part_list.part_number = part_list_supplier.part_id)
left join sales on (part_list.part_number = sales.number
group by part_list.part_number;

Upvotes: 0

Related Questions