SQL Return 0 for empty rows

hi, how about:

select sum(Antal_Best) as antalBest, sum(Antal_Lev) as antalLev, Artikel_Nr  
from TreN_order where Order_Datum = '2008-08-05'
and (artikel_Nr IN(010,012,013,014,015,018,110,130) or artikel_Nr  is null)
group by artikel_nr order by Artikel_Nr

select count(*)
from TreN_order where Order_Datum = '2008-08-05'
and artikel_Nr IN(010,012,013,014,015,018,110,130)
group by artikel_nr order by Artikel_Nr

try


select sum(Antal_Best) as antalBest, sum(Antal_Lev) as antalLev,
A.Artikel_Nr  
from
(Select 010 artikel_nr
union Select 012
union Select 013
union Select 014
union Select 015
union Select 018
union Select 110
union Select 130) A left outer join
TreN_order ON
A.artikel_Nr = TreN_order.artikel_Nr
And Order_Datum = '2008-08-05'
and artikel_Nr IN(010,012,013,014,015,018,110,130)
group by A.artikel_nr order by A.Artikel_Nr

Limbeck: The row(s) that are not inte my answer, (but I want them to be) is not Null, they dont just exist.
chapmandew: This one only shows how many rows with values in.
appari: No output at all

what is the datatype of  artikel_Nr ?
if it is character type change the values 010 etc. to '010' etc..

and try this SQL

Select A.Artikel_Nr  , antalBest, antalLev
From
(Select 010 artikel_nr
union Select 012
union Select 013
union Select 014
union Select 015
union Select 018
union Select 110
union Select 130) A Left Outer Join
select sum(Antal_Best) as antalBest, sum(Antal_Lev) as antalLev,
Artikel_Nr  
from
TreN_order Where Order_Datum = '2008-08-05'
and artikel_Nr IN(010,012,013,014,015,018,110,130)
group by artikel_nr ) B
on A.Artikel_Nr = B.Artikel_Nr
order by A.Artikel_Nr

appari has it about right.  I think you should be getting some kind of result.


SELECT SUM(TN_o.Antal_Best) as antalBest, sum(TN_o.Antal_Lev) as antalLev,
    a_Ns.Artikel_Nr  
FROM (
    SELECT 010 AS artikel_Nr UNION ALL
    SELECT 012 UNION ALL
    SELECT 013 UNION ALL
    SELECT 014 UNION ALL
    SELECT 015 UNION ALL
    SELECT 018 UNION ALL
    SELECT 110 UNION ALL
    SELECT 120
) AS a_Ns
LEFT OUTER JOIN TreN_order TN_o ON
    TN_o.artikel_Nr = a_Ns.artikel_Nr
WHERE TN_o.Order_Datum >= '2008-08-05' --AND TN_o.Order_Datum < '2008-08-06' --<<-- if Order_Datum is datetime, add this other condition
GROUP BY a_Ns.artikel_nr
ORDER BY a_Ns.artikel_nr

Try This:

Select Artikel_Nr, Sum(Antal_Best), Sum(Antal_Lev)
From (Select Artikel_Nr, Antal_Best, Antal_Lev   
From TreN_order
where Order_Datum = '2008-08-05'
and artikel_Nr IN('010','012','013','014','015','018','110','130')
UNION ALL SELECT '010', 0, 0
UNION ALL SELECT '012', 0, 0
UNION ALL SELECT '013', 0, 0
UNION ALL SELECT '014', 0, 0
UNION ALL SELECT '015', 0, 0 
UNION ALL SELECT '018', 0, 0 
UNION ALL SELECT '110', 0, 0 
UNION ALL SELECT '120', 0, 0) as a

Select allOpen in new window

I added the single quotes around 010, etc. so that they are not converted to 10 as system will treat like a number.  If the value is actual 010 then want to make sure you it is looking up appropriately.  If these are really numbers, meaning 010 is stored as 10 in table, then remove quotes.

Regards.

It works to run the "ScottPletcher:" But it still doesnt give the "empty" rows same result as my first SQl-Question

Appari. That works :-) Execpt one thing, I got Null i the rows having no values for Antal_Best, Antal_Lev.
How do I get 0 there instead

Aha I found it myself (ISNULL)

Lol.  I forgot the Group By statement when I copied back into post.  Good answer Appari.  It is what I was saying above.  To avoid nulls, you can just add in the 0's like I have in my post so that there is always a value Antal_Best and Antal_Lev to be summed.