android sqlite select avg multiple columns

Question

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

l33t · Accepted Answer · 2013-12-03 18:19:50Z

1

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

answered Dec 3, 2013 at 18:19

l33t

20.3k20 gold badges117 silver badges192 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mosty Mostacho Over a year ago

Haha, funny way to calculate this.

l33t Over a year ago

@MostyMostacho I decided to use COALESCE before I had a solution in mind :P

d2000k Over a year ago

thank you all. now it works. did not know the function COALESCE good code: String lista_ge = "SELECT *, (COALESCE(tr_max_m, 0) + COALESCE(tr_max_t, 0)) /(COALESCE(tr_max_m/tr_max_m, 0) + COALESCE(tr_max_t/tr_max_t, 0)) AS media_max FROM ...

Mosty Mostacho · Accepted Answer · 2013-12-03 18:35:10Z

1

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

edited Dec 3, 2013 at 18:35

answered Dec 3, 2013 at 18:09

Mosty Mostacho

43.6k16 gold badges99 silver badges124 bronze badges

Comments

LS_ᴅᴇᴠ · Accepted Answer · 2013-12-04 10:18:12Z

0

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

answered Dec 4, 2013 at 10:18

LS_ᴅᴇᴠ

11.2k1 gold badge27 silver badges46 bronze badges

Collectives™ on Stack Overflow

android sqlite select avg multiple columns

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related