CODEWITHSUNDEEP
sqloracle-database
UcanDoIt
UcanDoIt

Reputation: 1845

How can i remove results with that give me a zero sum, not using a subquery?

I can't have sums that give me a zero value. So i used a subquery to achive it. Problem is that sub-queries are not fast. How would you make it to be more efficient?

select * from (
      SELECT a,
             b, 
             c, 
             sum(d) as  ftd 
            FROM items f 
            WHERE f.category = 'ANY' AND 
                  f.version = 'V2018' AND 
                  f.month = '01' AND 
                  f.year = '2017'
            Group by  f.a, 
                     f.b, 
                     f.c 
            ORDER BY f.a, 
                     f.b, 
                     f.c
    )where ftd!=0;

Upvotes: 0

Views: 51

Answers (2)

Virgil Ionescu
Virgil Ionescu

Reputation: 337

You can resolve this by using HAVING clause:

SELECT a,
b, 
c, 
sum(d) as  ftd 
FROM items f 
WHERE f.category = 'ANY' AND 
f.version = 'V2018' AND 
f.month = '01' AND 
f.year = '2017'
Group by  f.a, 
f.b, 
f.c 
HAVING SUM(f.d)<>0
ORDER BY f.a, 
f.b, 
f.c

Upvotes: 0

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

Use HAVING

  SELECT a,
         b, 
         c, 
         sum(d) as  ftd 
        FROM items f 
        WHERE f.category = 'ANY' AND 
              f.version = 'V2018' AND 
              f.month = '01' AND 
              f.year = '2017'
        Group by  f.a, 
                 f.b, 
                 f.c 
       HAVING sum(d) != 0

Upvotes: 3

Related Questions