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

• ypercubeᵀᴹ: Do you want a list of all names and the sum of the ItemQty for every group (of rows with same Name)?
• user2125348: @ypercube Yes and yes. But only the sum of ItemQty where used is null. but I still want to show the names even when Used is null, just under sum Should show 0.
• ypercubeᵀᴹ: How many rows does the table have?
• ypercubeᵀᴹ: And how many rows have null in the Used column (and how many not null)?
• Darius X.: Seems like you're optimizing something that is already optimum. Why are you trying to avoid CASE? Your solution tests the null on each row and goes through the table twice. I'd stick with the very first SQL.

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 ;

• user2125348: This is what I have already, I entered it wrong in the question (fixed now). but what I am looking for is a better performing version of this that doesn't need to do the line by line case function.
• ypercubeᵀᴹ: If you had this query, why did you post a question with another, that does not even run?
• user2125348: It was an accident. Basically is there any way to return 2 queries on top of each other? That's what would be perfect.
• user2125348: I have updated my question with a possible solution. That shouldn't require the case statement, therefor shouldn't need to evaluate Row by row?
• ypercubeᵀᴹ: No, your solution would give some Names 2 times in the result, one with 0 and one with Qty>0. Did you try the above with the index? If it is not efficient, then we can discuss.

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.