We help IT Professionals succeed at work.

# SQL Return 0 for empty rows

on
1,473 Views
Hi.

I have this SQL question

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 order by Artikel_Nr

I want it to always return the numbers of rows that are in the IN(**) and if thats empty it shall return 0 for the value.

If for example 010 and 012 have no data then my question will only return 6 rows, but I want it to return
8 rows all the time.

Comment
Watch Question

## View Solution Only

Commented:

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
CERTIFIED EXPERT
Awarded 2008
Awarded 2008

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

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

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

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

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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
Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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
``````
Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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.

Commented:
It works to run the "ScottPletcher:" But it still doesnt give the "empty" rows same result as my first SQl-Question
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)

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

Commented:
Aha I found it myself (ISNULL)
Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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.
Unlock the solution to this question.

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.