MYSQL Query with Multiple Selects from Same Table

Question

Getting an error

Operand should contain 1 column(s)

PK is ID

The table just dumps data in to the table

need to get the earliest date qty and the latest date qty and display on the same column

Any help appreciated

SELECT ebx_r_history.ItemNumber,
       (SELECT r.QuantitySold as newqty, r.lastupdate as lu
          FROM ebx_r_history r
         WHERE ebx_r_history.ItemNumber = r.ItemNumber AND ebx_r_history.SKU = r.SKU
         ORDER BY r.LastUpdate ASC
         LIMIT 1),
       (SELECT r.QuantitySold as newqty, r.lastupdate as lu
          FROM ebx_r_history r
         WHERE ebx_r_history.ItemNumber = r.ItemNumber AND ebx_r_history.SKU = r.SKU
         ORDER BY r.LastUpdate DESC
         LIMIT 1)
FROM
ebx_r_history
GROUP BY ebx_r_history.ItemNumber,
         ebx_r_history.SKU
ORDER BY ebx_r_history.LastUpdate 

Answer 1

This version may offer a simplified and faster alternative for you. The inner query for "AllItems" does both a min and max of the last update on a per-item number/sku basis, although I believe they would be one-in-the-same record.

So now, join that results back to the history data by item/sku and only those that match either the min or max date. If a true date/time, there would expect to only be one anyhow, vs just a date-only. So, since there would be 2 possible records (one for the min, one for the max), I am applying a MAX( IIF( )) for each respective matching the minimum and maximum dates respectively and must retain the group by clause.

Note, if you are dealing with date-only entries, or possibilities of the exact same item/sku and lastupdate are the same to the second, then you would need an approach more towards limit 1 per ascending/descending basis.

SELECT
      AllItems.ItemNumber,
      AllItems.SKU,
      AllItems.MinUpdate,
      MAX( IIF( rh.lastupdate = AllItems.MinUpdate, rh.Quantity.Sold, 0 )) as QtyAtMinDate,
      AllItems.MaxUpdate,
      MAX( IIF( rh.lastupdate = AllItems.MaxUpdate, rh.Quantity.Sold, 0 )) as QtyAtMaxDate
   from 
      ( SELECT 
              r.ItemNumber,
              r.SKU,
              MIN( r.lastupdate ) as MinUpdate,
              MAX( r.lastupdate ) as MaxUpdate
          FROM 
             ebx_r_history r
          group by 
             r.ItemNumber,
             r.SKU ) AllItems
      JOIN ebx_r_history rh
         ON AllItems.ItemNumber = rh.ItemNumber
         AND AllItems.SKU = rh.SKU
         AND ( rh.lastUpdate = AllItems.MinUpdate
            OR rh.lastUpdate = AllItems.MaxUpdate )

group by AllItems.ItemNumber, AllItems.SKU

Per another answer where you were only looking to IGNORE items within the most recent 14 days, you can just add a WHERE clause to the inner query similar via

 WHERE r.LastUpdate >= CURDATE() - INTERVAL 14 DAY

If your history table has an auto-incrementing ID column, AND the respective transactions have the lastUpdate sequentially stamped, such as when they are added and not modified by any other operation, then you could just apply similar but MIN/MAX of the ID column, then join back TWICE on the ID and just each row ONCE such as...

SELECT
      AllItems.ItemNumber,
      AllItems.SKU,
      rhMin.LastUpdate as MinUpdate,
      rhMin.QuantitySold as MinSold,
      rhMax.LastUpdate as MaxUpdate,
      rhMax.QuantitySold as MaxSold
   from 
      ( SELECT 
              r.ItemNumber,
              r.SKU,
              MIN( r.AutoIncrementColumn ) as MinAutoID,
              MAX( r.AutoIncrementColumn ) as MaxAutoID
          FROM 
             ebx_r_history r
          group by 
             r.ItemNumber,
             r.SKU ) AllItems
      JOIN ebx_r_history rhMin
         ON AllItems.MinAutoID = rhMin.AutoIncrementColumn
      JOIN ebx_r_history rhMax
         ON AllItems.MaxAutoID = rhMax.AutoIncrementColumn
   order by
      rhMax.LastUpdated

Answer 2

Try something like this:

SELECT r1.ItemNumber,
  (
    SELECT r.QuantitySold
    FROM ebx_r_history r
    WHERE r1.ItemNumber = r.ItemNumber
      AND r1.SKU = r.SKU
    ORDER BY r.LastUpdate ASC LIMIT 1
    ) AS earliestDateQty,
  (
    SELECT r.QuantitySold
    FROM ebx_r_history r
    WHERE r1.ItemNumber = r.ItemNumber
      AND r1.SKU = r.SKU
    ORDER BY r.LastUpdate DESC LIMIT 1
    ) AS latestDateQty
FROM ebx_r_history r1
GROUP BY r1.ItemNumber,r1.SKU
ORDER BY 3

You had a couple of errors. you were getting two columns inside the inner selects, and you had a couple of places where you might get the error for ambiguous column name.