Oracle SQL Distinct Clause not presenting distinct values

Question

I have a script when I'm trying to select locations in an inventory where quantity of said location is <= 5. The query is complete, now I'm trying to do some fine tuning, and what I'm running into now is when I use the distinct clause I am still receiving duplicate records in the same column. I do know the column next to the first are unique, but I thought distinguishing distinct and one column would roll over to next related to said column.

Here is my code:

select DISTINCT bin.scannable_id as bin, 
  bi.bin_id as case1,
  pallet.scannable_id as pallet,       
 -- bi.isbn as fcsku, 
  nvl(fs.asin,bi.isbn) as asin, 
  sum(bi.quantity) as quantity, 
  pallet.creation_date as received_date 
  from containers bin 
  join containers pallet on pallet.containing_container_id = bin.container_id 
  join containers case on case.containing_container_id = pallet.container_id 
  join bin_items bi on bi.container_id = case.container_id 
  left join fcskus fs on fs.fcsku = bi.isbn 
  where bin.scannable_id like 'R-1-T%'
  and bi.quantity <= '5'
  group by bin.scannable_id, pallet.scannable_id, bi.bin_id, bi.owner,bi.isbn,nvl(fs.asin,bi.isbn), pallet.creation_date
  order by sum(bi.quantity);

My output, which is obviously showing duplicate records in the scannable_id column:

Correct Formatting Thanks to conrad.

    select DISTINCT bin.scannable_id as bin,   
  pallet.scannable_id as pallet,       
   nvl(fs.asin,bi.isbn) as asin, 
  sum(bi.quantity) as quantity   
  from containers bin 
  join containers pallet on pallet.containing_container_id = bin.container_id 
  join containers case on case.containing_container_id = pallet.container_id 
  join bin_items bi on bi.container_id = case.container_id 
  left join fcskus fs on fs.fcsku = bi.isbn 
  where bin.scannable_id like 'R-1-T%'
  having sum(bi.quantity) <= '5'
  group by bin.scannable_id, pallet.scannable_id, nvl(fs.asin,bi.isbn), bi.quantity
  order by sum(bi.quantity);

Answer 1

As said on the comments you dont need a DISTINCT if you have the group by statement. And format your date field because depending on your oracle client configuration it will not show you the entire date format (e.g. date time). So try with this:

select  bin.scannable_id as bin, 
        bi.bin_id as case1,
        pallet.scannable_id as pallet,       
        nvl(fs.asin,bi.isbn) as asin, 
        to_char(pallet.creation_date, 'yyyy-mm-dd') as received_date 
        sum(bi.quantity) as quantity, 
  from containers bin 
     join containers pallet on pallet.containing_container_id = bin.container_id 
     join containers case on case.containing_container_id = pallet.container_id 
     join bin_items bi on bi.container_id = case.container_id 
     left join fcskus fs on fs.fcsku = bi.isbn 
 where bin.scannable_id like 'R-1-T%'
   and bi.quantity <= '5'
 group by bin.scannable_id, 
          pallet.scannable_id, 
          bi.bin_id, 
          bi.owner,
          bi.isbn,
          nvl(fs.asin,bi.isbn), 
          to_char(pallet.creation_date, 'yyyy-mm-dd')
 order by sum(bi.quantity);

Answer 2

bi.bin_id is different for each row, so you do only have distinct results in your resultset.

• distinct is applied to the final visible resultset (once the to_char etc. functions are processed)
• distinct is redundant if you already use a group by expression

Solution: skipp the bi.bin_id column from your select expression.

Your logic is also confusing. You want to know the SUM of all the bi.* elements. To do so you cannot group by bi.bin_id nor any field from the bi table. This is the reason why your quantity result is always 1.