Counting all other types but the current one

Question

Hi, I'm trying to write this query, that would calculate the average value of all the columns except the one that contains the type value, which I'm grouping the whole query by.

So for 4 types for example, each column in the resulting table will contain the average of all the other three type's values, i need to exclude the current type's rows.

As an example, if I was to calculate each type's average value for itself, the query would look like:

SELECT

SUM(some value) / COUNT(TYPE)

FROM TEMPTABLE
GROUP BY TYPE

Now I'm trying to calculate the other three's total average. Thanks.

Answer 1

You can do one query to get the distinct types, and LEFT JOIN the same table, checking for type-inequality:

SELECT t1.type,
       SUM(t2.some_value) / COUNT(t2.type)
FROM ( SELECT DISTINCT type FROM temptable ) t1
LEFT JOIN temptable t2 ON ( t1.type <> t2.type )
GROUP BY t1.type

Since you only want the average, you could replace the line

FROM ( SELECT DISTINCT type FROM temptable ) t1

by

FROM temptable t1

but the first solution might perform better, since the number of rows is reduced earlier.

Answer 2

The starting point here is to make a cartesian join between your types and your temptable (guessing your tables structure is : type(id, type), valueTable(id, type_id, some_value))

The following query

SELECT t.type, SUM(vt.someValue) / COUNT (*) AS sum FROM type t, valueTable vt WHERE vt.type_id != t.id GROUP BY t.type

should do the trick.

Answer 3

Should work on Sybase too:

SELECT
   SUM(some value) / SUM(CASE WHEN TYPE = 1 THEN 1 ELSE 0 END)
FROM TEMPTABLE
GROUP BY TYPE

Greets Flo

Answer 4

Will this do what you need?

(Possibly with another CASE statement to avoid divide by zero errors if there is a possibility none of a type might be returned, I've also not explicitly accounted for the case that type is NULL)

SELECT

SUM(CASE WHEN TYPE <> 'Type1' THEN someValue ELSE 0 END) / 
     SUM(CASE WHEN TYPE = 'Type1' THEN 1 ELSE 0 END) AS T1,

SUM(CASE WHEN TYPE <> 'Type2' THEN someValue ELSE 0 END) / 
     SUM(CASE WHEN TYPE = 'Type2' THEN 1 ELSE 0 END) AS T2,

SUM(CASE WHEN TYPE <> 'Type3' THEN someValue ELSE 0 END) / 
     SUM(CASE WHEN TYPE = 'Type3' THEN 1 ELSE 0 END) AS T3,

SUM(CASE WHEN TYPE <> 'Type4' THEN someValue ELSE 0 END) / 
     SUM(CASE WHEN TYPE = 'Type4' THEN 1 ELSE 0 END) AS T4
FROM TEMPTABLE

Answer 5

I think that you can just use this:

SELECT type, avg(col_01)
FROM myTable
GROUP BY type