group by ignore null value

  • hpares

    Valued Member

    Points: 62

    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: 112354

    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: 396247

     

    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

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply