0

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
5
  • Do you want a list of all names and the sum of the ItemQty for every group (of rows with same Name)? Apr 10, 2014 at 16:30
  • @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. Apr 10, 2014 at 16:32
  • How many rows does the table have? Apr 10, 2014 at 16:42
  • And how many rows have null in the Used column (and how many not null)? Apr 10, 2014 at 17:02
  • 1
    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.
    – Darius X.
    Apr 10, 2014 at 18:18

2 Answers 2

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 ;
5
  • 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. Apr 10, 2014 at 16:40
  • If you had this query, why did you post a question with another, that does not even run? Apr 10, 2014 at 16:43
  • It was an accident. Basically is there any way to return 2 queries on top of each other? That's what would be perfect. Apr 10, 2014 at 16:50
  • 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? Apr 10, 2014 at 16:58
  • 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. Apr 10, 2014 at 16:59
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.