Databases
StackOverflow2013
Postsdbo
POSQL Help : How to produce data output from multiple tables that need to be summed and find out the MAX date?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Help : How to produce data output from multiple tables that need to be summed and find out the MAX date?
    text
    copied!<p>In order to produce "Production Report" I need to join many tables together and find out sum of product line's qty that was produced in each lot and what is the latest activity that each line produced. </p> <p>I can sum data and find out the MAX activity in separate SQL query, but when i joined them, the query show error. So could you please help me. Thank you very much.</p> <p>My tables are below :</p> <pre><code>tbl_pdtn_startup pdtn_st_id | pd_id | pdtn_st_date 3 | 001 | 3/9/12 4 | 002 | 4/9/12 tbl_pdtn_sizecolor pdtn_szcl_id | pdtn_st_id | pdtn_st_color | pdtn_st_size | pdtn_st_qty_est 23 | 3 | Blue | S | 100 26 | 3 | Pink | S | 100 27 | 3 | Green | S | 100 28 | 4 | Blue | M | 200 30 | 4 | Green | M | 200 tbl_production pdtn_id | pdtn_szcl_id | pdtn_st_id | pdtn_date | pd_making_id | lb_id 24 | 23 | 3 | 4/9/12 | 1 | 12 26 | 23 | 3 | 5/9/12 | 2 | 12 27 | 26 | 3 | 4/9/12 | 1 | 15 28 | 27 | 3 | 5/9/12 | 1 | 15 &lt;--MAX(pdtn_date) 29 | 28 | 4 | 4/9/12 | 4 | 15 30 | 30 | 4 | 6/9/12 | 5 | 12 &lt;--MAX(pdtn_date) tbl_product pd_id | Name 001 | product A 002 | product B tbl_pdWk_process pd_making_id | wk_stage | pd_id | cost 1 | step 1 | 001 | 12 2 | step 2 | 001 | 15 3 | step 1 | 003 | 12 4 | step 1 | 002 | 18 5 | step 2 | 002 | 10 tbl_labor lb_id | lb_name 12 | john 15 | Mary </code></pre> <p>I need the output as show below :</p> <pre><code>pd_id | pd_name | pdtn_st_date | sum(pdtn_st_qty_est)*|MAX(pdtn_date)**| wk_stage | lb_name 001 | product A| 3/9/12 | 300 | 5/9/12 | step 1 | Mary 002 | product B| 4/9/12 | 400 | 6/9/12 | step 2 | John </code></pre> <p>Note<br/> * Sum(pdtn_st_qty_est) comes from sum of all pdtn_st_qty_est in the same pdtn_st_id (production_startup_id) (for ex:- Blue=100, Pink=100, Green=100 for pdtn_st_id = 3) <br/><br/> ** MAX(pdtn_date) comes from the latest activity that worker has been done for each product lot(for ex:- 5/9/12 from pdtn_st_id = 3 ) </p> <p>Below was my attempt that tried to join the 3 mains table together but fail..</p> <pre><code>SELECT U.pd_id, A.SumOfpdtn_st_qty_est, S.MaxOfpdtn_date FROM (tbl_pdtn_startup AS U INNER JOIN [SELECT Sum(tbl_pdtn_sizecolor.pdtn_st_qty_est) AS SumOfpdtn_st_qty_est FROM tbl_pdtn_sizecolor GROUP BY tbl_pdtn_sizecolor.pdtn_st_id]. AS A ON U.pdtn_st_id = A.pdtn_st_id) INNER JOIN [SELECT Max(tbl_production.pdtn_date) AS MaxOfpdtn_date FROM tbl_production GROUP BY tbl_production.pdtn_st_id]. AS S ON (A.pdtn_szcl_id = S.pdtn_szcl_id) AND (U.pdtn_st_id = S.pdtn_st_id) </code></pre> <p><br><br><b>My next attempt:</b><br> I've came up with the idea of making separate query and join them together, which it can produce partial data that i want. Now there's only 2 data that i couldn't retrieve it right. which are lb_name and wk_stage. So could anybody help me. Thank you very much.</p> <p>q_maxdate</p> <pre><code>SELECT tbl_pdtn_startup.pdtn_st_id, tbl_pdtn_startup.pd_id, Max(tbl_production.pdtn_date) AS MaxOfpdtn_date, tbl_product.pd_name, tbl_pdtn_startup.pdtn_st_pdNote, tbl_pdtn_startup.pdtn_st_date FROM (((tbl_pdtn_startup INNER JOIN tbl_pdtn_sizecolor ON tbl_pdtn_startup.pdtn_st_id=tbl_pdtn_sizecolor.pdtn_st_id) INNER JOIN tbl_production ON tbl_pdtn_sizecolor.pdtn_szcl_id=tbl_production.pdtn_szcl_id) INNER JOIN tbl_product ON tbl_pdtn_startup.pd_id=tbl_product.pd_id) INNER JOIN tbl_pdWk_process ON (tbl_production.pd_making_id=tbl_pdWk_process.pd_making_id) AND (tbl_product.pd_id=tbl_pdWk_process.pd_id) GROUP BY tbl_pdtn_startup.pdtn_st_id, tbl_pdtn_startup.pd_id, tbl_product.pd_name, tbl_pdtn_startup.pdtn_st_pdNote, tbl_pdtn_startup.pdtn_st_date; </code></pre> <p>then i joined it in this query...</p> <pre><code>SELECT q_maxdate.pdtn_st_id, Sum(tbl_pdtn_sizecolor.pdtn_st_qty_est) AS SumOfpdtn_st_qty_est, q_maxdate.MaxOfpdtn_date, q_maxdate.pd_name, q_maxdate.pdtn_st_pdNote, q_maxdate.pd_id, q_maxdate.pdtn_st_date FROM q_maxdate INNER JOIN tbl_pdtn_sizecolor ON q_maxdate.pdtn_st_id = tbl_pdtn_sizecolor.pdtn_st_id GROUP BY q_maxdate.pdtn_st_id, q_maxdate.MaxOfpdtn_date, q_maxdate.pd_name, q_maxdate.pdtn_st_pdNote, q_maxdate.pd_id, q_maxdate.pdtn_st_date; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

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