group by ignore null value

Question

group by ignore null value

hpares

SSC Enthusiast

Points: 112
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.

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

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

Ant-Green SSC Guru Points: 113507 More actions · Answer 1

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: 398882 More actions · Answer 2

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

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning