CODEWITHSUNDEEP
mysqlsql
NeoS
NeoS

Reputation: 75

Group condition in last data mysql

I have a data like this :

Table LOT
+-------+--------+
|Lot_id | Prod_id|
+-------+--------+
| LOT-1 | Prd-1  |
| LOT-1 | Prd-2  |
| LOT-1 | Prd-3  |
| LOT-2 | Prd-4  |
+-------+--------+

Table Process
+-------+--------+--------+------------+----------+
|proc_id|proc_cat|proc_seq|proc_prod_id|t_proc_qty|
+-------+--------+--------+------------+----------+
|   1   | Proc-A |   1    |    Prd-1   |   100    |
|   2   | Proc-H |   2    |    Prd-1   |   100    |
|   3   | Proc-D |   3    |    Prd-1   |   100    |
|   4   | Proc-A |   1    |    Prd-2   |   100    |
|   5   | Proc-H |   2    |    Prd-2   |   100    |
|   6   | Proc-D |   3    |    Prd-2   |    20    |
|   7   | Proc-Q |   4    |    Prd-2   |    20    |
|   8   | Proc-A |   1    |    Prd-3   |   100    |
|   9   | Proc-H |   2    |    Prd-3   |   100    |
|  10   | Proc-D |   3    |    Prd-3   |    50    |
|  11   | Proc-O |   1    |    Prd-4   |    80    |
|  12   | Proc-F |   2    |    Prd-4   |    80    |
|  13   | Proc-H |   3    |    Prd-4   |    80    |
+-------+--------+--------+------------+----------+

And i want data like this if i want select just LOT=LOT-1. table LOT joined to table Process and data is accumulated sum(t_proc_qty) from last proc_seq each proc_prod_id and group by proc_cat and order by proc_seq

    +--------+--------+----------+
    |proc_cat|proc_seq|t_proc_qty|
    +--------+--------+----------+
    | Proc-D |   3    |   150    |->accumulated from Prd-1 and prd-3 in last process is seq 3
    | Proc-Q |   4    |    20    |->accumulated from Prd-2 in last process is seq 4
    +--------+--------+----------+

What queries I use in MySQL ?

I stucked in query 

SELECT proc_cat, proc_seq, SUM(t_proc_qty) 
FROM Process 
LEFT JOIN Lot ON proc_prod_id=Prod_id 
WHERE Lot_id='LOT-1' 
GROUP BY proc_prod_id 
ORDER BY proc_seq DESC LIMIT 1

this schema for trial query SQLFiddle

Upvotes: 2

Views: 65

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94894

From table Process you want the records with the highest proc_id per proc_prod_id:

select *
from process
where not exists 
(
  select * 
  from process later 
  where later.proc_prod_id = process.proc_prod_id
  and later.proc_id > process.proc_id
);

From this data you want an aggregate per proc_cat and proc_sec. And you also want to consider only prod_id for 'LOT-1' in table LOT.

The complete query:

select proc_cat, proc_seq, sum(t_proc_qty)
from process
where proc_prod_id in (select prod_id from lot where lot_id = 'LOT-1')
and not exists 
(
  select * 
  from process later 
  where later.proc_prod_id = process.proc_prod_id
  and later.proc_id > process.proc_id
)
group by proc_cat, proc_seq
order by proc_cat, proc_seq;

SQL fiddle: http://sqlfiddle.com/#!9/1fa3fd/5

Upvotes: 1

Related Questions