January 22, 2009 at 8:50 am
Hope someone can help me out, by just looking at the query.
If I run the query as is I get:
sample just one row; 20081021, 3.42953468114138, 7.04471015930176, 1.82109773159027, 82.3088323473931
Now if I uncomment the code in the query I get a different result for the sum field
20081021, 3.42953468114138, 7.04471015930176, 1.82109773159027, 246.926497042179, -8.75071482638885
I think somehow the join is causing this, but I don't know why.
select CONVERT(VARCHAR(10),METER_DATA_TIMESTAMP,112) as Date,
AVG(meter_data_value) as KWAVG,
Max(METER_DATA_VALUE) AS KWMAX,
Min(METER_DATA_VALUE) AS KWMIN,
SUM(METER_DATA_VALUE)/4 AS KWH--,
--AVG(WEATHER_DATA_15_TEMPERATURE)-65 AS DDTEMP
FROM meter_data_AFFINITY1
--left join WEATHER_DATA_15 on meter_data_timestamp = weather_data_15_timestamp
WHERE METER_DATA_METER_ID=1 AND
METER_DATA_QUANTITY_ID=1 AND
METER_DATA_TIMESTAMP between '10/21/2008' and '11/10/2008'
GROUP BY CONVERT(VARCHAR(10),METER_DATA_TIMESTAMP,112) ORDER BY DATE ASC
Thanks,
KS
January 22, 2009 at 8:56 am
Unless the relationships between the table are one-to-one, joining tables and then running aggregates will usually give different results than the same aggregates run on each table separately. That's because the join will result in multiple rows having the same values.
For example:
The average of a table with,
1
2
3
in the rows, is 2, because the sum is 6 and there are three rows.
But, if that table is joined to a table on some key value, so that the results look like this:
1
1
1
2
3
3
the average becomes 1.83, because the sum is now 11 and we're looking at 6 rows.
Since that's what joins often do, you have to account for that in your query. A common way is to pre-aggregate the data, then to join to the other table, instead of aggregating and joining in the same step.
If you turn each part of the query into a sub-query, then join those, that should give you what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 22, 2009 at 11:47 am
One more thing. My initial problem was I left out teh weather location in the where clause.
Once I added that then all my figures were correct.
But another issue had popped up.
In the Weather_data_15 table there are not entries for the date of 11/10/2008
But in the table meter_data_affinity1 there is data for that date, but the data is not comming back.
I thought a left join was suppose to being back everything even if there are no entries in the right table?
I even tried using a right join, but same results.
select CONVERT(VARCHAR(10),METER_DATA_TIMESTAMP,112) as Date,
AVG(meter_data_value) as KWAVG,
Max(METER_DATA_VALUE) AS KWMAX,
Min(METER_DATA_VALUE) AS KWMIN,
SUM(METER_DATA_VALUE)/4 AS KWH,
AVG(WEATHER_DATA_15_TEMPERATURE)-65 AS DDTEMP
FROM meter_data_AFFINITY1
left join WEATHER_DATA_15 on meter_data_timestamp = weather_data_15_timestamp
WHERE METER_DATA_METER_ID=1 AND
METER_DATA_QUANTITY_ID=1 AND
METER_DATA_TIMESTAMP between '10/21/2008' and '11/10/2008' and
WEATHER_DATA_15_LOCATION = 'KNCCORNE2'
GROUP BY CONVERT(VARCHAR(10),METER_DATA_TIMESTAMP,112)
ORDER BY DATE ASC
Thanks again,
KS
January 22, 2009 at 11:58 am
Having columns from the second table in the Where clause effectively turns an outer join into an inner. Move that part to the Join, instead of the Where, and it should do what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 22, 2009 at 12:02 pm
Forgive my ignorance, but how?
The only commonality between the two tables is the timestamp.
January 22, 2009 at 12:05 pm
select CONVERT(VARCHAR(10),METER_DATA_TIMESTAMP,112) as Date,
AVG(meter_data_value) as KWAVG,
Max(METER_DATA_VALUE) AS KWMAX,
Min(METER_DATA_VALUE) AS KWMIN,
SUM(METER_DATA_VALUE)/4 AS KWH,
AVG(WEATHER_DATA_15_TEMPERATURE)-65 AS DDTEMP
FROM meter_data_AFFINITY1
left join WEATHER_DATA_15 on meter_data_timestamp = weather_data_15_timestamp
and WEATHER_DATA_15_LOCATION = 'KNCCORNE2'
WHERE METER_DATA_METER_ID=1 AND
METER_DATA_QUANTITY_ID=1 AND
METER_DATA_TIMESTAMP between '10/21/2008' and '11/10/2008' GROUP BY CONVERT(VARCHAR(10),METER_DATA_TIMESTAMP,112)
ORDER BY DATE ASC
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 22, 2009 at 12:07 pm
Your a god send. You rock thanks very much.
January 22, 2009 at 2:46 pm
You're very welcome. Helping people out is the main reason I visit this site.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply