CODEWITHSUNDEEP
sqlt-sqlsybasesumaverage
Halo
Halo

Reputation: 1532

Counting all other types but the current one

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.

Upvotes: 0

Views: 400

Answers (5)

Florian Reischl
Florian Reischl

Reputation: 3856

Should work on Sybase too:

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

Upvotes: 0

Jamie LaMorgese
Jamie LaMorgese

Reputation: 44

I think that you can just use this:

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

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453328

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

Upvotes: 0

Killerwhile
Killerwhile

Reputation: 46

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.

Upvotes: 0

Peter Lang
Peter Lang

Reputation: 55524

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.

Upvotes: 1

Related Questions