CODEWITHSUNDEEP
sql-server-2008
Paulo Bueno
Paulo Bueno

Reputation: 2569

SQL apply formula to AVG values

given the pseudo-table:

+-----+---------------------+------+
| tag | data                | read |
+-----+---------------------+------+
| A   | 2013-10-10 15:00:00 | 1345 |
+-----+---------------------+------+
| A   | 2013-10-10 15:15:00 | 3454 |
+-----+---------------------+------+
| A   | 2013-10-10 15:30:00 | 2345 |
+-----+---------------------+------+
| A   | 2013-10-10 15:45:00 | 1132 |
+-----+---------------------+------+
| B   | 2013-10-10 15:00:00 | 6234 |
+-----+---------------------+------+
| B   | 2013-10-10 15:15:00 | 5432 |
+-----+---------------------+------+
| B   | 2013-10-10 15:30:00 | 4563 |
+-----+---------------------+------+
| B   | 2013-10-10 15:45:00 | 5432 |
+-----+---------------------+------+

Is it possible to apply the following equation using SQL only?

Example:

result=AVG(A)-(AVG(B)+AVG(C))

or 

result=AVG(A)+AVG(B)

grouped by date?

Upvotes: 0

Views: 54

Answers (2)

sqlint
sqlint

Reputation: 1089

It should calculate result

Here's a Demo on SqlFiddle.

select (AVG(case when tag = 'A' then read end) + AVG(case when tag = 'B' then read end)) 'result', data
from TBL
group by data

Upvotes: 1

GregA100k
GregA100k

Reputation: 1395

You can select a sum or avg for each tag individually and then select with the operation you want on each of those individual queries

select (select SUM([read]) from table where tag = 'A') + 
  (select SUM([read]) from table where tag = 'B')

Upvotes: 0

Related Questions