self join issue, i think?

Question

I have one table(could only have one) that looks like seen below.

It contains a plant code, an article number and batches related to that article.

plant - code - batch - value - volume - added_date
A1      1000   A1      10      20       date
A1      2000   A7      20      15       date
A1      1000   A1      5       10       later than first A1 batch
A2      200    A8      10      9        date
A1      2000   A10     20      20       date

How could I present this information like this: for each plant and code, show the DISTINCT number of batches and the volume for only the latest added batch(note that code 1000 in plant A1 is showing only 10 as that is the volume of the latest added batch).

plant - code - batches - volume
  A1      1000   1         10     
  A1      2000   2         35
  A2      200    1         9

I got some help from a guy here earlier, and that query works great until I tried to get the volume from only the latest batches if there is two batches with same number for the same product on the same plant.

SELECT code, plant,
    COUNT(DISTINCT(batch)) as Batches,
    SUM(value) as TotalValue,
    SUM(volume) as TotalVolume,
    SUM(value * risk) as TotalRisk,
FROM lists 
GROUP BY code, plant

I guess you could do it with some concat and self joins but I am not skilled enough to work with only one table :/

Thanks in advance!

Edit: Well, actually the answer that Adrian linked to solved the described issue...however it seems to be to hard to do make everything else work with that solution. I did only describe a part of my "problem", what I thought was enough to then solve the rest myself. But I was wrong. Not only do I need the latest added batch and volume, I also need to do some SUM operations on the batches. I am thinking that it may be best for me to place the data into two different tables and work from there.

You have on your hands a [tag:greatest-n-per-group] problem. I lack the time to write a tailor-made answer for you, so take your time to read here: http://stackoverflow.com/a/7745635/570191 . It will guide you to the right answer — Adriano Carneiro, Feb 13 '12 at 19:30
Thanks Adrian, I will read that! First I was afraid you would say that there was no solution for my problem :) — Josef, Feb 13 '12 at 19:45
Don't worry, there's more one way to solve your problem :) Don't forget to upvote if the answer there helps you — Adriano Carneiro, Feb 13 '12 at 19:53
of course not. At a first glance it seems to be what I am looking for but need some time to adjust it to my needs. I'll add it to favourites for now :) — Josef, Feb 13 '12 at 20:12
You are right. BTW, you'll be glad to take your time and adjust it yourself. Since it's not a ready answer, you will learn the concepts of the problem. — Adriano Carneiro, Feb 13 '12 at 20:15
Indeed! And when I feel comfortable with that I need to adjust it to work with zend frameworks select object. But that's another story and most likely another post here :) — Josef, Feb 13 '12 at 20:22

Michael Berkowski · Answer 1 · 2012-02-13T19:53:37.970

0

Here's a possibility using a JOIN subquery which retrieves the volume by the MAX(added_date):

SELECT
  l.code,
  l.plant,
  COUNT(DISTINCT l.batch) AS Batches,
  lvol.vol AS volume
FROM
  lists l JOIN (
    SELECT code, plant, volume AS vol FROM lists GROUP BY code, plant HAVING added_date = MAX(added_date)
  ) lvol ON l.code = lvol.code AND l.plant = lvol.plant AND l.volume = lvol.volume
GROUP BY l.code, l.plant

edited Feb 13 '12 at 19:53

answered Feb 13 '12 at 19:26

Michael Berkowski

267,341
46
444
390

That did not work. It ignored the plant for which we have a product with two of the same batches, in this case plant A1 was not shown at all :/ – Josef Feb 13 '12 at 19:44
@Josef See the change to the join `ON` clause. Added `AND l.volume = lvol.volume` – Michael Berkowski Feb 13 '12 at 19:54

Akhil · Answer 2 · 2012-02-13T20:36:24.660

0

SELECT  t.*,
        (SELECT  volume 
           FROM  lists
          WHERE  plant = t.plant
            AND  code  = t.code
          ORDER BY added_date DESC
          LIMIT 1   --Take the latest record for the plant & code
          ) AS volume                  
  FROM  (
            SELECT code, plant,
                COUNT(DISTINCT(batch)) as Batches,
                SUM(value) as TotalValue,
                SUM(value * risk) as TotalRisk
            FROM lists 
            GROUP BY code, plant
        ) AS t

edited Feb 13 '12 at 20:36

answered Feb 13 '12 at 19:29

Akhil

7,570
1
24
23

That was better. Just remove the "," after TotalRisk. However, I need to do the same for the value even if that was included in my example. I thought I was able to figure it out myself when I had a the basics. But with this query it was not that simple. – Josef Feb 13 '12 at 20:18

self join issue, i think?

2 Answers2

Linked