case and group by problem

Hi I have this code below it gives me the right results but there are load o
f
null values. Is there a way to group by on a case statement to elimiinate
these nulls to smarten up ther report
select ,c.name ,c.custno,c.salesno, case catno
when 'n' then sum(qtyshp* netprice) end as 'Cat-N', case catno
when 'L' then sum(qtyshp* netprice) end as 'Cat-L', case catno
when 'G' then sum(qtyshp* netprice) end as 'Cat-G', case catno
when 'I' then sum(qtyshp* netprice) end as 'Cat-I'
from ius_cust c
inner join ius_detail d
on d.custno=c.custno
inner join ius_prods p
on p.prod=d.prod
where invdate > '2005-01-01'
group by c.name,c.custno,c.salesno,p.catno
thanks for any help
Sammychange the case catno
when 'L' then sum(qtyshp* netprice) end as
to case catno
when 'L' then sum(qtyshp* netprice) else 0 end as
put else 0 in between
http://sqlservercode.blogspot.com/
Sammy wrote:
> Hi I have this code below it gives me the right results but there are load
of
> null values. Is there a way to group by on a case statement to elimiinate
> these nulls to smarten up ther report
> select ,c.name ,c.custno,c.salesno, case catno
> when 'n' then sum(qtyshp* netprice) end as 'Cat-N', case catno
> when 'L' then sum(qtyshp* netprice) end as 'Cat-L', case catno
> when 'G' then sum(qtyshp* netprice) end as 'Cat-G', case catno
> when 'I' then sum(qtyshp* netprice) end as 'Cat-I'
> from ius_cust c
> inner join ius_detail d
> on d.custno=c.custno
> inner join ius_prods p
> on p.prod=d.prod
> where invdate > '2005-01-01'
> group by c.name,c.custno,c.salesno,p.catno
>
> thanks for any help
> Sammy|||Sammy
Can you show us your table's structure?
create table #test
(
col1 int,
col2 int,
col3 char(1)
)
insert into #test values (1,10,'h')
insert into #test values (1,40,'h')
insert into #test values (1,20,'s')
insert into #test values (2,20,'h')
insert into #test values (2,10,'h')
insert into #test values (2,850,'a')
select col1,sum(case when col3='h' then col2 end),
sum(case when col3='h' then col2 end)
from #test
group by col1
"Sammy" <Sammy@.discussions.microsoft.com> wrote in message
news:CE89E103-FD29-4B44-A12B-D10C7D836A13@.microsoft.com...
> Hi I have this code below it gives me the right results but there are load
> of
> null values. Is there a way to group by on a case statement to elimiinate
> these nulls to smarten up ther report
> select ,c.name ,c.custno,c.salesno, case catno
> when 'n' then sum(qtyshp* netprice) end as 'Cat-N', case catno
> when 'L' then sum(qtyshp* netprice) end as 'Cat-L', case catno
> when 'G' then sum(qtyshp* netprice) end as 'Cat-G', case catno
> when 'I' then sum(qtyshp* netprice) end as 'Cat-I'
> from ius_cust c
> inner join ius_detail d
> on d.custno=c.custno
> inner join ius_prods p
> on p.prod=d.prod
> where invdate > '2005-01-01'
> group by c.name,c.custno,c.salesno,p.catno
>
> thanks for any help
> Sammy|||The nulls are from empty result sets, so try try this to get zero
amounts
SUM (CASE cat_no WHEN 'n'
THEN (qty_shp* net_price)
ELSE 0.00 END) AS cat-N
Do not put the alias names in single quotes since that is proprietary
syntax that will screw up other tools and portability.|||On Wed, 21 Dec 2005 08:23:03 -0800, Sammy wrote:

>Hi I have this code below it gives me the right results but there are load
of
>null values. Is there a way to group by on a case statement to elimiinate
>these nulls to smarten up ther report
>select ,c.name ,c.custno,c.salesno, case catno
>when 'n' then sum(qtyshp* netprice) end as 'Cat-N', case catno
>when 'L' then sum(qtyshp* netprice) end as 'Cat-L', case catno
>when 'G' then sum(qtyshp* netprice) end as 'Cat-G', case catno
>when 'I' then sum(qtyshp* netprice) end as 'Cat-I'
>from ius_cust c
>inner join ius_detail d
>on d.custno=c.custno
>inner join ius_prods p
>on p.prod=d.prod
>where invdate > '2005-01-01'
>group by c.name,c.custno,c.salesno,p.catno
Hi Sammy,
Try if this works:
SELECT c.name, c.custno, c.salesno,
SUM(CASE catno WHEN 'n' THEN qtyshp * netprice ELSE 0 END) AS
"Cat-N",
SUM(CASE catno WHEN 'L' THEN qtyshp * netprice ELSE 0 END) AS
"Cat-L",
SUM(CASE catno WHEN 'G' THEN qtyshp * netprice ELSE 0 END) AS
"Cat-G",
SUM(CASE catno WHEN 'I' THEN qtyshp * netprice ELSE 0 END) AS
"Cat-I"
FROM ius_cust AS c
INNER JOIN ius_detail AS d
ON d.custno = c.custno
INNER JOIN ius_prods AS p
ON p.prod = d.prod
WHERE invdate > '20050101' -- Note the recommended format
GROUP BY c.name, c.custno, c.salesno, p.catno
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 21 Dec 2005 08:33:07 -0800, --CELKO-- wrote:

>The nulls are from empty result sets, so try try this to get zero
>amounts
> SUM (CASE cat_no WHEN 'n'
> THEN (qty_shp* net_price)
> ELSE 0.00 END) AS cat-N
>Do not put the alias names in single quotes since that is proprietary
>syntax that will screw up other tools and portability.
Hi Joe,
The alternative you recommend is portable - it results in an error on
ALL platforms. ;->
Try enclosing it in double quotes: ... AS "cat-N"
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)