remove SQL duplicate values, distinct not working

Question

SELECT BM.PUser, 
   BM.Desc, BM.Price, 
   BM.Info,
   CAST (CASE 
      WHEN BI.Closed = 'E' then BQ.Qty-BQ.Consign-(SUM(BD.QtySold))
      ELSE BQ.Qty-BQ.Consign
   END as int) as Stock
FROM BINVOICE BI
 , BDETAIL BD
 , BQTY BQ
 , BMASTER BM
WHERE (BD.User = BI.User)
   AND 
  (BQ.PartNo = BD.PartNo)
   AND
  (BQ.PartNo = BM.PartNo)
   AND
  (BM.Price > 0.01)
   AND
  (BM.Active = 'Y')
GROUP BY BM.PUser, BM.Price,
     BM.Desc, BM.Info, 
     BQ.Consign, BQ.Qty,
     BI.Closed

My issue is that I want only one of each PUser to display, but some of them appear multiple times. I believe the CASE I have made is where they are coming from. I am not quite sure how to get around this. Using SELECT DISTINCT did not work for me. Any guidance would be greatly appreciated. It is used on NexusDB.

I it's only PUser you want "distinct", then that's the only column you should specify in the GROUP BY! (All other columns in the select list should be arguments to aggregate functions.) — jarlh, Mar 21 '15 at 07:49
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Mar 21 '15 at 10:05
i am using an old version of reportbuilder because that is what i have been supplied with. it is basically requiring me to have everything in the group by and will not work if i take any of them out. same thing goes for the way i have comma separated my tables. — paralyzed21, Mar 21 '15 at 15:27

score 0 · Answer 1 · answered Mar 21 '15 at 04:56

Use Distinct SELECT DISTINCT column_name,column_name FROM table_name;

SELECT Distinct  BM.PUser, 
   BM.Desc, BM.Price, 
   BM.Info,
   CAST (CASE 
      WHEN BI.Closed = 'E' then BQ.Qty-BQ.Consign-(SUM(BD.QtySold))
      ELSE BQ.Qty-BQ.Consign
   END as int) as Stock
FROM BINVOICE BI
 , BDETAIL BD
 , BQTY BQ
 , BMASTER BM
WHERE (BD.User = BI.User)
   AND 
  (BQ.PartNo = BD.PartNo)
   AND
  (BQ.PartNo = BM.PartNo)
   AND
  (BM.Price > 0.01)
   AND
  (BM.Active = 'Y')
GROUP BY BM.PUser, BM.Price,
     BM.Desc, BM.Info, 
     BQ.Consign, BQ.Qty,
     BI.Closed

@utility might have not been used properly – xrcwrn Mar 21 '15 at 05:10 — xrcwrn, Mar 21 '15 at 05:10

remove SQL duplicate values, distinct not working

1 Answers1