1

my SQL skills aren't strong enough to figure this out, hope someone can help. This is on SQLite for Android:

I have a table 'attempts':

attempt_id | word | score | attempt_time
----------------------------------------
1          | w1   | 1     | 2011-09-01
2          | w1   | 2     | 2011-09-02
3          | w2   | 1     | 2011-09-02
4          | w3   | 1     | 2011-09-03
5          | w4   | 0     | 2011-09-03
6          | w1   | 0     | 2011-09-04

Each word has a score associated with it; I want to find the number of words that have a score >=1 for each day. Scores are preserved from day to do, so a score for day t should be reflected in day t+1. When a word goes to score 0, it should not be counted. The result should look something like this:

attempt_time | count(word)
--------------------------
2011-09-01   | 1              // w1 (1)
2011-09-02   | 2              // w1, w2 (2)
2011-09-03   | 3              // w1, w2, w3 (3)
2011-09-04   | 2              // w2, w3 (2) (w1 now has score 0)

There are lots of attempt_times, so you can't loop through them individually. I suspect I need to use a subquery to sum the each date and the previous dates, but still need a way to discard duplicates (e.g. only count w1 once).

It should do something like this, but need all the dates rather than just one:

select count(distinct word) from attempts where score > 0 
and attempt_time <= '2011-09-03';

> 3

My alternative is to build a separate table that just keeps track of the totals on each day, but then I have to keep the data in sync.

Thanks much.

Edit: I've tried to add some more details to make the question more clear. Hope it helps.

Shawn Lauzon
  • 6,234
  • 4
  • 35
  • 46

3 Answers3

1

If you need to aggregate and display the counts based on date you're going to want to use the "Group By" clause. Try out the syntax below.

Select attempt_time, count(*) from attempts where score > 0 group by attempt_time

Here's another example of someone solving a similar problem. SELECT *, COUNT(*) in SQLite

Community
  • 1
  • 1
Carth
  • 2,303
  • 1
  • 17
  • 26
  • Close, but not quite. The result of yours gives row 3 (date of 2011-09-03) as 1 (counting w3). I need 2011-09-03 to be the ACCUMULATED number of words with score > 0, which includes the words from previous days (w1, w2, w3 = 3). – Shawn Lauzon Sep 16 '11 at 16:09
0

Firstly, I think you mean your results to be:

attempt_time | count(word)
--------------------------
2011-09-01   | 1              // w1 has score 1
2011-09-02   | 3              // w1 increased to 2, plus w2 has score 1
2011-09-03   | 5              // w1 and w2 same, w3 has score 1, w4 has no score
2011-09-04   | 5              // no change

But you'd not get a Sept 4th date as it's not in the DB. But anyhoo, You need this:

set @csum := 0;

select attempt_time, (@csum := @csum + score) as score from attempts where score > 0 and attempt_time <= '2011-09-03' group by attempt_time;

Which I just simply stole from this: Create a Cumulative Sum Column in MySQL (Big up to https://stackoverflow.com/users/50552/andomar)

Erm, is that what you need? :)

Tris...

Community
  • 1
  • 1
Beertastic
  • 691
  • 1
  • 9
  • 27
  • Actually no; I don't want to find the sum of the points, I want to find the number of words with score >= 1. The '2' in the second row corresponds to two words (w1 and w2), not the sum of their points (which as you point out correctly, is 3) – Shawn Lauzon Sep 15 '11 at 17:48
  • Then shouldn't row 3 be: `2011-09-03 | 1 // w1 and w2 same, w3 has score 1, w4 has no score` – Beertastic Sep 16 '11 at 05:45
  • `2011-09-03 | 1 // w1 and w2 same, w3 has score 1, w4 has no score` Shouldn't tow 3 be as above.. or is the word count added up? – Beertastic Sep 16 '11 at 05:46
  • The word count is added up. The count column should be the accumulated number of words from the beginning of time. This is a game of number of words you know, and "knowing" for me means the number of words with a score >= 1. A higher score simply means knowing it "better", but you know it with a score >= 1. – Shawn Lauzon Sep 16 '11 at 15:58
  • So row 1 is w1 (1), row 2 is w1,w2 (2), row 3 is w1,w2,w3 (3), and row 4 is still w1,w2,w3 (3) – Shawn Lauzon Sep 16 '11 at 16:00
0

It doesn't seem like there's a good way to do this.

Shawn Lauzon
  • 6,234
  • 4
  • 35
  • 46