SQL Alternative to Inner Join
I have a table that has data that appears as:
Staion Date Temperature
A 2015-07-31 8
B 2015-07-31 6
C 2015-07-31 8
A 2003-02-21 4
B 2003-02-21 7
C 2003-02-21 7
For each date, I need to create arrays so that it has the following combination:
c1 = (A + B)/2, c2 = (A + B + C)/3 and c3 = (B + C)/2
Right. I am doing three different inner join
on the table itself and making the final inner join
one to achieve the result:
Date c1 c2 c3
2015-07-31 7 7.33 7
2003-02-21 5.5 6 7
Is there a cleaner way to do this?
+3
source to share
4 answers
You can use rotation and compute on the rotated data as shown below:
select [Date], c1 = (A+B)/2.0, c2 = (A+B+C)/3.0, C3 = (B+C)/2.0 from
( select * from #yourstation ) s
pivot (max(temparature) for station in ([A], [B], [C])) p
Input table:
create table #yourStation (station char(1), date date, Temparature int)
insert into #yourStation (station, date, Temparature) values
('A','2015-07-31', 8 )
,('B','2015-07-31', 6 )
,('C','2015-07-31', 8 )
,('A','2003-02-21', 4 )
,('B','2003-02-21', 7 )
,('C','2003-02-21', 7 )
+1
source to share
No need for JOIN
, you can just use GROUP BY
the aggregation function as well:
WITH CTE AS
(
SELECT [Date],
MIN(CASE WHEN Staion = 'A' THEN Temperature END) A,
MIN(CASE WHEN Staion = 'B' THEN Temperature END) B,
MIN(CASE WHEN Staion = 'C' THEN Temperature END) C
FROM dbo.YourTable
GROUP BY [date]
)
SELECT [Date],
(A+B)/2 c1,
(A+B+C)/3 c2,
(B+C)/2 c3
FROM CTE;
+6
source to share
You can do this with just two concatenations and almost literally the formulas you provided:
declare @t table (Station char(1) not null,[Date] date not null, Temperature int not null)
insert into @t(Station,[Date],Temperature) values
('A','20150731',8),
('B','20150731',6),
('C','20150731',8),
('A','20030221',4),
('B','20030221',7),
('C','20030221',7)
select
B.[Date],
c1 = (A.Temperature + B.Temperature)/2.0,
c2 = (A.Temperature + B.Temperature + C.Temperature)/3.0,
c3 = (B.Temperature + C.Temperature)/2.0
from
@t B
inner join
@t A
on
B.[Date] = A.[Date]
inner join
@t C
on
B.[Date] = C.[Date]
where
A.Station = 'A' and
B.Station = 'B' and
C.Station = 'C'
Result:
Date c1 c2 c3
---------- --------------- ----------- ----------
2015-07-31 7.000000 7.333333 7.000000
2003-02-21 5.500000 6.000000 7.000000
+2
source to share