CODEWITHSUNDEEP
androidsqliteaverage
d2000k
d2000k

Reputation: 29

android sqlite select avg multiple columns

I have a problem with a query of sqlite. I want to do is the average of two columns and place the result in a new column. example:

id | max_ma  |  max_ta  | avg_max (new column)

1  | 100     |  102     |   101  ==> (100+102)/2

2 |  100     |  null    |   100 ==> (100+0)/1 INGNORE NULL

wrong code:

public Cursor list() {
String lista_ge = "SELECT *, AVG(tr_max_m + tr_max_t) AS media_max FROM bdt_registro ORDER BY tr_fecha DESC, _id DESC";
return db.rawQuery(lista_ge, null);
}

or

public Cursor list() {
String lista_ge = "SELECT *, ((tr_max_m + tr_max_t)/COUNT(*)) AS media_max FROM bdt_registro ORDER BY tr_fecha DESC, _id DESC";
return db.rawQuery(lista_ge, null);
}

Thanks for your interest

Upvotes: 0

Views: 578

Answers (3)

LS_ᴅᴇᴠ
LS_ᴅᴇᴠ

Reputation: 11151

Another solution based on function IFNULL and...

... "un-aggregated" aggregated function COUNT:

SELECT *,
    (IFNULL(max_ma, 0)+IFNULL(max_ta, 0))/(COUNT(max_ma)+COUNT(max_ta)) AS avg_max
FROM bdt_registro
GROUP BY id;

... CASE:

SELECT *,
    (IFNULL(max_ma, 0)+IFNULL(max_ta, 0)) / (CASE WHEN max_ma IS NULL THEN 0 ELSE 1 END + CASE WHEN max_ta IS NULL THEN 0 ELSE 1 END) AS avg_max
FROM bdt_registro;

» SQL Fiddle

Upvotes: 0

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Probably the best approach will be to use a lot of cases:

SELECT max_ma, max_ta,
  CASE WHEN max_ma IS NULL THEN
    CASE WHEN max_ta IS NULL THEN 0
    ELSE max_ta END
  ELSE
    CASE WHEN max_ta IS NULL THEN max_ma
    ELSE (max_ma + max_ta) / 2 END
  END avg_max
FROM user_address

Fiddle here.

By the way, I noticed you were trying to use AVG. If you're wondering how you could have used that function then this will give you an idea. It won't be faster than the previous approach because it adds more calculation, though:

SELECT id,
  max(CASE WHEN kind = 1 THEN aMax END) max_ma,
  max(CASE WHEN kind = 2 THEN aMax END) max_ta,
  avg(aMax) aMax
FROM (
  SELECT id, max_ma aMax, 1 kind FROM user_address
  UNION ALL
  SELECT id, max_ta, 2 FROM user_address
) s
GROUP BY id

Upvotes: 1

l33t
l33t

Reputation: 19937

Use the COALESCE function:

SELECT *,
(COALESCE(tr_max_m, tr_max_t, 0) + COALESCE(tr_max_t, tr_max_m, 0)) / 2 AS media_max
FROM
bdt_registro ORDER BY tr_fecha DESC, _id DESC
  • If both are non_NULL you get: (tr_max_t + tr_max_m) / 2
  • If tr_max_m is NULL you get: (tr_max_t + tr_max_t) / 2 = tr_max_t
  • If tr_max_t is NULL you get: (tr_max_m + tr_max_m) / 2 = tr_max_m
  • If both are NULL you get: (0 + 0) / 2 = 0

Upvotes: 1

Related Questions