CODEWITHSUNDEEP
sqlsqlite
something_new
something_new

Reputation: 91

Getting data from 2 different tables

I have two tables that contain data from different devices and I want to combine them per date (YMD format). Each table has multiple DeviceRowId's but, in this example, I am only interested in 7, 13 and 14.

I'm selecting a sqlite3 database and I will export the result to csv

Table Temperature

DeviceRowId, Temp_Avg,   Date
7,           5.2,        2015-01-01
7,           5.6,        2015-01-02
7,           5.3,        2015-01-03
7,           4.7,        2015-01-04
7,           4.9,        2015-01-05
4,           19.0,       2015-01-01
4,           19.6,       2015-01-02
4,           18.9,       2015-01-03
4,           19.1,       2015-01-04

Table Meter

DeviceRowId, Value, Date
13,          13200, 2015-01-01
13,          11200, 2015-01-02
13,          13700, 2015-01-03
13,          14200, 2015-01-04
14,          540,   2015-01-01
14,          570,   2015-01-02
14,          660,   2015-01-03
14,          590,   2015-01-04
14,          700,   2015-01-05
19,          350,   2015-01-01
19,          680,   2015-01-02
19,          920,   2015-01-03
19,          310,   2015-01-04
19,          700,   2015-01-05

I cannot guarantee that all devices have an entry on every single day, so device 13 doesn't have an entry on 2015-01-05. What I'm trying to achieve is:

Date, Dev7, Dev13, Dev14
2015-01-01, 5.2, 13200, 540
2015-01-02, 5.6, 11200, 570
2015-01-03, 5.3, 13700, 660
2015-01-04, 4.7, 14200, 590
2015-01-05, 4.9, 0, 700

This will let me plot per date the values of device 7, 13 and 14.

Thanks

Upvotes: 1

Views: 56

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You can approach this with conditional aggregation -- after using union all to bring the data together:

select date,
       max(case when DeviceRowId = 7 then temp_avg end) as Dev7,
       max(case when DeviceRowId = 13 then temp_avg end) as Dev13,
       max(case when DeviceRowId = 14 then temp_avg end) as Dev14
from (select Date, DeviceRowId, temp_avg
      from table1
      where DeviceRowId in (7, 13, 14)
      union all
      select Date, DeviceRowId, value
      from table2
      where DeviceRowId in (7, 13, 14)
     ) t
group by date
order by date;

Upvotes: 2

Related Questions