Any SQL nerds here?

NYSpeed Automotive NYSpeed Off Topic
1

I have a table with 4 columns all INTs

ID (auto increment)
TIMESTAMP (the same for all 6 elementids)
ELEMENTID (one of 6 values)
COUNT (0-400)

To pull my data, I am running multiple queries but trying to clean it up. I want to combine values in my parent query and getting stuck.

SELECT timestamp,count FROM stats WHERE elementid=10 ORDER BY timestamp DESC LIMIT 12
SELECT timestamp,count FROM stats WHERE elementid=11 ORDER BY timestamp DESC LIMIT 12

In this query, I would like to turn count which selects the value for the row into one query so essential my SELECT returns the timestamp and then the sum of two SELECTS as total_count for element=10 and element=11

2

I will ask our DBA when I get to work.

3

This has stumped a few people. I am throwing my head at it and my friend last night was trying to do it with JOIN but we ran out of time.

Here is a better example if that doesn’t make sense

ID TIMESTAMP ELEMENTID COUNT
1 10000000 10 50
2 10000000 11 100
3 10000000 12 60
4 10000000 13 90
5 10000120 10 100
6 10000120 11 100
7 10000120 12 0
8 10000120 13 50

My query I want to return is:

TIMESTAMP TOTAL_COUNT (element ID 10 count + element ID 11 count grouped with the timestamp they match)
10000000 150
10000120 200

4

Not 100% sure what you’re trying to do. This?

SELECT timestamp t, (
SELECT sum(count) FROM stats WHERE elementid IN (10, 11) AND timestamp = t
) as total_count FROM stats ORDER BY timestamp DESC LIMIT 12

5

Ah you put me right on the correct track. Thank you!

SELECT timestamp t, (
SELECT sum(count) FROM stats WHERE elementid IN (10, 11) AND timestamp = t
) as total_count FROM stats WHERE trunk=10 OR trunk=11 GROUP BY timestamp ORDER BY timestamp DESC LIMIT 12

6

:tup:

7

The only thing this query is slow as hell on my server. Need to optimize it somehow or just beef up the query on the server.