0

I want to add the results of two separate counting SQlite queries. Suppose I have 2 tables named entries and scores and have 2 queries:

SELECT COUNT(1) FROM entries WHERE
    key NOT IN (SELECT key FROM scores)

SELECT COUNT(1) FROM scores WHERE
    value <= threshold

Maybe I could do something like this to get the sum of their results:

SELECT COUNT(1) from (
    SELECT key FROM entries WHERE
        key NOT IN (SELECT key FROM scores)
    UNION ALL
    SELECT key FROM scores WHERE
        value <= threshold
)

But is this a little too inefficient? This is called pretty often and may interfere with the UI's smoothness.

Thank you.

[EDIT] What I'm actually trying to do:

I'm making an app to help learning vocabulary. The entries table keeps 'static' data about word type, definition, etc. The scores table keeps information about how well you've learned the words (e.g. performance, scheduled next review time)

To check for the number of remaining words to learn/review, I count how many words do not exist in the scores table yet (i.e. never touched) or when the accumulated score is pretty low (i.e. needs reviewing).

The reason I don't merge those 2 tables into 1 (which would make my life much easier) is because sometimes I need to update the entries table either by inserting new words, deleting a few words, or updating their content, and I haven't found a simple way to do that. If I simply do INSERT OR REPLACE, I will lose the information about scores.

Community
  • 1
  • 1
Phil
  • 5,595
  • 5
  • 35
  • 55
  • by `COUNT(1)` you mean ...? It only accepts `*` or a column identifier. [See here](http://www.sqlite.org/lang_aggfunc.html). –  May 22 '11 at 09:32
  • it's a trick I learned somewhere to count the number of rows :D – Phil May 22 '11 at 09:35
  • Perhaps say, in English, what the *final intent* is :) To count the number of rows `COUNT(*)` should be used. –  May 22 '11 at 09:36
  • COUNT(1) and COUNT(\*) are effectively the same. COUNT(\*) means count the rows selecting all of the columns and COUNT(1) means count all of the rows selecting the integer 1. – Gyan aka Gary Buyn May 22 '11 at 09:45
  • @pst: I've updated my post explaining my goal. – Phil May 22 '11 at 09:47

1 Answers1

2

I think you're looking for a UNION. A union combines the results from two queries. Try this (sorry it isn't tested, I don't have access to SQLite):

SELECT COUNT(1) FROM
(
    SELECT 1
    FROM entries
    WHERE key NOT IN (SELECT key FROM scores)
    UNION ALL
    SELECT 1
    FROM scores
    WHERE scores.value <= threshold
)

After reading the edit in your question explaining what you need to do, I think a JOIN would be more appropriate. This is a way of combining two tables into one query. Something like this:

SELECT COUNT(1)
FROM entries
LEFT JOIN score
    ON score.key = entries.key
WHERE score.value <= threshold
OR score.key is null
Gyan aka Gary Buyn
  • 12,242
  • 2
  • 23
  • 26
  • `UNION` will removed duplicate rows. It must be `UNION ALL`. See [Compound Select Statements](http://www.sqlite.org/lang_select.html). –  May 22 '11 at 09:32
  • @pst: I'm new to sql. Could you help me if you spot any mistake? Thank you very much :D – Phil May 22 '11 at 09:34
  • @Po I've added a comment to the main post. –  May 22 '11 at 09:35