We help IT Professionals succeed at work.

SQL Return 0 for empty rows

1,473 Views
Last Modified: 2012-05-05
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

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


Author

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

Scott PletcherSenior 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
Kevin CrossChief 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

Open in new window

Kevin CrossChief 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.

Author

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)
UNLOCK SOLUTION

Author

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

Author

Commented:
Aha I found it myself (ISNULL)
Kevin CrossChief 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.
Join our community and discover your potential

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.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.