How to optimize query

performanceperformance-tuningquery-performancesqlite

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

Best Answer

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 ;
Related Question