How to optimize query

Question

I'm using SQLite3 and the following query, It seems like there should be a better performing way to write this query; Is there?

Select Name,
    Sum(Case 
       When 
          Used 
             Is Null 
       Then 
          ItemQty
       Else 
          0 
       End) As QtyNotUsed
From 
   ContainersReceived
Group By
   Name

Basically my thought (that I can't realize) is something like:

Select 
    Distinct Name
From 
   ContainersReceived 

combined with

Select 
   Sum(ItemQty)
From
   ContainersReceived 
Where
    Used Is Null

The result I want is a list of all Names and how much is in stock from the table ContainersReceived.

UPDATE:

Possible solution is to use:

Select 
    Name, 
    Sum(ItemQty)
From ContainersReceived
Where Used Is Null
Group By Name
UNION ALL
Select 
    Name, 
    0
From ContainersReceived
Where Used Is Not Null
Group By Name

Answer 1

It's not clear what you want but this may be it:

SELECT
    Name,
    SUM(CASE WHEN Used IS NULL 
           THEN ItemQty 
           ELSE 0 
        END) AS QtyNotUsed
FROM 
    ContainersReceived
GROUP BY
    Name ;

To be efficient, add an index on (Name, Used, ItemQty)

---

If you have another index, on (Used, Name, ItemQty), this query will be even more efficient - but it will not show Names that have no row with Used IS NULL:

SELECT
    Name,
    SUM(ItemQty) AS QtyNotUsed
FROM 
    ContainersReceived
WHERE
    Used IS NULL 
GROUP BY
    Name ;

Answer 2

I think you know about performance of using UNION instead of OR, but it's not in this case vs. using CASE that just add an inline filter.

In your second query you will have two Sort actions as a side effect of having two GORUP BY statements and also two Filter actions with adding a Concatenation by using UNION.

Note:
The count of actions is not a matter in performance in all cases.

I think, If data of ContainersReceived table grows to a huge rows, your second query will be slower than your first query.