How do I get the sum of a column across multiple keys?
I have data that looks like this:
id int (11) primary key auto_increment
key int (2)
type int (2)
data int (4)
timestamp datetime
There are 5 different keys - 1,2,3,4,5 and three types - 1,2,3
Data is put in continuously against a key and of a particular type.
What I need to extract is a sum of the data for a particular type (say,
type 1) across all 5 keys (1,2,3,4,5) so it is a sum of exactly 5 records.
I only want to sum the latest (max(timestamp) values (there are 5 of them)
of data for each key, but they may all have different timestamps.
Something like this....
SELECT sum(data) FROM table WHERE type='1' AND timestamp=(SELECT
max(timestamp FROM table WHERE type='1' GROUP BY key)
Or something like that. That isn't even close of course. I am completely
lost on this one. it feels like I need to group by key but the syntax
eludes me. Any suggestions are appreciated.
EDIT: additional info:
if: 'data' is temperature. 'key' is day of the week. 'type' is morning,
noon or night
So the data might look like
morning mon 70 (timestamp)
noon tue 78 (timestamp)
morning wed 72 (timestamp)
night tue 74 (timestamp)
morning thu 76 (timestamp)
noon wed 77 (timestamp)
night fri 78 (timestamp)
noon tue 79 (timestamp)
If these are in timestamp order (desc) and I want the sum of most recent
noon temps for all five days, the result would be: 155 in this case since
the last noon was also tuesday and it was earlier and thus, not included.
Make sense? I want sum of 'data' for any key, specific type, latest
timestamp only. In this example, I would be summing at most 7 pieces of
data.
No comments:
Post a Comment