group by ignore null value

• hpares

  Valued Member

  Points: 62

  More actions

  January 17, 2020 at 1:16 pm

  #3716278

  Hi,

  I am not actually a programming but my job did need to write a bit of sql queries. Hope someone can help me

  here is my code

  select i.item_code,i.cost_standard,(select item_price.item_price where item_price.price_group = 'wholesale') as A,(select item_price.item_price where item_price.price_group = 'vnd')as B

  from item i left join item_price on i.item_code = item_price.item_code

  where i.item_code in ('SOWL-SGL17173-C1')

  result as follow

  item_code cost_standard A B

  SOWL-SGL17173-C1 63.300000 NULL NULL

  SOWL-SGL17173-C1 63.300000 NULL NULL

  SOWL-SGL17173-C1 63.300000 NULL NULL

  SOWL-SGL17173-C1 63.300000 NULL NULL

  SOWL-SGL17173-C1 63.300000 NULL 71.280000

  SOWL-SGL17173-C1 63.300000 110.000000 NULL

  What i want is all in single line as below and null is not showing

  item_code cost_standard A B

  SOWL-SGL17173-C1 63.300000 110.000000 71.280000

   

  Please advise.

• anthony.green

  SSC Guru

  Points: 112378

  More actions

  January 17, 2020 at 2:01 pm

  #3716301

  Without a full sample mock up of the tables in question, is it simply just a case of adding MAX and GROUP BY

  select 
  i.item_code,
  i.cost_standard,
  max((select item_price.item_price where item_price.price_group = 'wholesale')) as A,
  max((select item_price.item_price where item_price.price_group = 'vnd'))as B
  from item i left join item_price on i.item_code = item_price.item_code
  where i.item_code in ('SOWL-SGL17173-C1')
  group by i.item_code, i.cost_standard

  http://www.sqlservercentral.com/articles/Best+Practices/61537/ If you can follow this link on how to post code for the best results, it helps us to get a feel for what your actually after.

• Grant Fritchey

  SSC Guru

  Points: 396339

  More actions

  January 17, 2020 at 2:21 pm

  #3716307

   

  Without the complete data set, I'm guessing a little, but try something like this:

  SELECT i.item_code,
         i.cost_standard,
         ipw.item_price,
         ipv.item_price
  FROM item AS i
      LEFT JOIN item_price AS ipw
          ON i.item_code = ipw.item_code
             AND ipw.price_group = 'wholesale'
      LEFT JOIN item_price AS ipv
          ON i.item_code = ipv.item_code
             AND ipv.price_group = 'vnd'
  WHERE i.item_code IN ( 'SOWL-SGL17173-C1' );

  You may still see NULL values depending on the data. Possibly adding a GROUP BY can eliminate those if needed.

  ----------------------------------------------------
  The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
  Theodore Roosevelt

  The Scary DBA
  Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
  Product Evangelist for Red Gate Software