SQL query: add missing Days in query result

Question

i'm not a sql-pro.

I have make this query:

SELECT CAST(SUBSTR(FEHLER.LosID,5,2) as INT), Sum(FEHLER.FehlerSumme)
FROM BLA.FEHLER FEHLER
WHERE (FEHLER.MAE=7) AND (FEHLER.LosID>16100100)
GROUP BY CAST(SUBSTR(FEHLER.LosID,5,2) as INT)
ORDER BY CAST(SUBSTR(FEHLER.LosID,5,2) as INT)

The result is for example:

(Column 1 = Day | Column 2 = Sum defective parts)
2 | 18
3 | 7
9 | 5

But I need somthing like this:

1 | (0 oder NULL)
2 | 18
3 | 7
4 |
5 |
6 |
7 |
8 |
9 | 5
...
31 |

So I want join the query on top with this but I was not successful.

SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=31

Is it a good idea to solve the problem with this subquery or have you better ideas? (Query in Excel, Oracle-SQL-Server)

Thanks for help

Oracle and "SQL Server" are two very different DBMS products. — user330315
– user330315, Commented Oct 30, 2016 at 9:49

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

Oracle

select      
    r.n, t."Sum defective parts"
from                   
   (SELECT LEVEL as n 
    FROM DUAL 
    CONNECT BY LEVEL <= 31) r
left join  
    (SELECT 
         CAST(SUBSTR(FEHLER.LosID, 5, 2) as INT) as "Day", 
         Sum(FEHLER.FehlerSumme) as "Sum defective parts"
     FROM 
         BLA.FEHLER FEHLER
     WHERE 
         (FEHLER.MAE = 7) AND (FEHLER.LosID > 16100100)
     GROUP BY 
         CAST(SUBSTR(FEHLER.LosID, 5, 2) as INT)) t ON t."Day" = r.n
 ORDER BY   
     r.n;

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 30, 2016 at 9:42

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MrBlue Over a year ago

Oh, ok. Thanks. I will try it tomorrow!

user5683823 Over a year ago

@MrBlue ...and if you prefer 0 instead of NULL, you can do that easily in the second line of code; instead of r.n you can select coalesce(r.n, 0). COALESCE returns its first argument, unless the first argument is NULL in which case it returns the second argument. (In Oracle that is also often written as nvl(r.n, 0).)

Gordon Linoff · Accepted Answer · 2016-10-30 12:50:42Z

0

If you have data in the table for each day, but the WHERE clause is filtering out certain day, then you can fix this using conditional aggregation:

SELECT CAST(SUBSTR(f.LosID, 5, 2) as INT),
       Sum(case when f.MAE = 7 AND f.LosID > 16100100
                then FEHLER.FehlerSumme
           end)
FROM BLA.FEHLER f
GROUP BY CAST(SUBSTR(f.LosID, 5, 2) as INT)
ORDER BY CAST(SUBSTR(FEHLER.LosID, 5, 2) as INT);

This will not work in all cases, but in many cases it offers a simple solution to this type of problem.

answered Oct 30, 2016 at 12:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Collectives™ on Stack Overflow

SQL query: add missing Days in query result

2 Answers 2

Oracle

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Oracle

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related