I need the total count of Products but i only have a line qty for each subproduct

• jeffshelix

  SSCrazy

  Points: 2257

  More actions

  April 12, 2018 at 12:52 pm

  #328294

  Paraphrasing my data -----My table only has the qty for each Prod ID. . . but i need the total number of apples .  . .. as in the last column.  I hope that is enough to go on.  Unseen columns have distinct data, so i cant do Distinct.  thanks  PROD_ID	Prod_Desc	Prod ID Qty		Prod ID Qty
  Granny Smith	Apple	44		44
  Granny Smith	Apple	44		609
  Granny Smith	Apple	44		581
  Granny Smith	Apple	44		70
  Granny Smith	Apple	44		82
  Macintosh	Apple	609		63
  Macintosh	Apple	609		1449
  Macintosh	Apple	609
  Macintosh	Apple	609		I want the total count of Apples
  Macintosh	Apple	609
  Green	Apple	581
  Green	Apple	581
  Green	Apple	581
  Green	Apple	581
  Green	Apple	581
  Fuji	Apple	70
  Fuji	Apple	70
  Fuji	Apple	70
  Fuji	Apple	70
  Fuji	Apple	70
  Gala	Apple	82
  Gala	Apple	82
  Gala	Apple	82
  Gala	Apple	82
  Gala	Apple	82
  HoneyCrisp	Apple	63
  HoneyCrisp	Apple	63
  HoneyCrisp	Apple	63
  HoneyCrisp	Apple	63
  HoneyCrisp	Apple	63

• Phil Parkin

  SSC Guru

  Points: 246960

  More actions

  April 12, 2018 at 1:43 pm

  #1986675

  Please provide sample DDL and INSERTs for your data. I think you may need to expand on the summing logic you require too, as I do not understand it as written.

  The absence of evidence is not evidence of absence
  - Martin Rees
  The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
  - Phil Parkin

• Jamie Cauldren

  SSC Enthusiast

  Points: 148

  More actions

  April 12, 2018 at 3:02 pm

  #1986684

  It's not quite clear what you are looking for, but I think this is what you want...

  -- You can do distinct, just dont do it against the entire record. Use a subquery to eliminate all the other columns first, then sum that subquery. If you just want a result that shows the total number of apples:
  select
      sum( PROD_ID_QTY ) TOTAL_APPLES
    from ( select distinct PROD_DESC, PROD_ID_QTY from <your table> ) a
   group by
       PROD_DESC

  Otherwise, as Phil said, the question is't quite clear. How you have the sample data layed out is somewhat confusing.