CODEWITHSUNDEEP
sqloracle-database
MrBlue
MrBlue

Reputation: 15

SQL query: add missing Days in query result

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

Upvotes: 1

Views: 99

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

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.

Upvotes: 0

David דודו Markovitz
David דודו Markovitz

Reputation: 44921

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;

Upvotes: 1

Related Questions