avg (sqlite)

[jashimu]

Hi all, I have a question about avg function. How do I query from 6 different tables and avegrage them. All of the tables have same number and type of coloumn. thanks in advance.

[James Shao]

I'm not sure if these work, but they should worth a try: Select (t1.col1, t2.col1,t3.col1, t4.col1, t5.col1, t6.col1)/6 From table1 as t1, table2 as t2, table3 as t3, table4 as t4, table5 as t5, table6 as t6 or use nested Select statement (use the sql AVG function on a grand total, and in the nested select statement you can define that the grand total is sum of individual columns). or use User-Defined-Function (you can create a function that takes in 6 parameters and return an average).

[jashimu]

Hi James Shao, Thanks for your answer, I tryied the first one it seems like not wroking with sqlite. I am not sure how to implement the second statement since I am (sorry to say) not good at sql yet. this is the first time I am working with sql. reading books and getting help form online this is how I am getting my work done. May be after this app I will have some knowledge about sql. if second statement does not wrok then third option is the one I will have to do. thanks very much for your feedback and help.

modified on Wednesday, January 13, 2010 11:19 AM

[loyal ginger]

You may want to use a sub-query to solve this problem. Try something like this: Suppose you have six tables t1, t2, ..., t6, with a column called c1 you want to average.

select avg(c1) from
(
select c1 from t1 where ...<put your condition here>
union all
select c1 from t2 where ...<put your condition here>
union all
select c1 from t3 where ...<put your condition here>
union all
select c1 from t4 where ...<put your condition here>
union all
select c1 from t5 where ...<put your condition here>
union all
select c1 from t6 where ...<put your condition here>
)

That will return the average of the column from all six tables.

[Niladri_Biswas]

Try this Assuming the tables have the following structure with data

Tbl1

ID1 NUM1
1 10
2 20

Tbl2

ID2 NUM2
1 11
2 22

Tbl3

ID3 NUM3
1 1
2 2
3 3

Tbl4

ID4 NUM4
1 41

Tbl5

ID5 NUM5
1 109

Tbl6

ID6 NUM6
1 61
2 62

Query:

SELECT AvgNum = AVG(N)FROM(
SELECT N=NUM1 FROM Tbl1 UNION ALL
SELECT NUM2 FROM Tbl2 UNION ALL
SELECT NUM3 FROM Tbl3 UNION ALL
SELECT NUM4 FROM Tbl4 UNION ALL
SELECT NUM5 FROM Tbl5 UNION ALL
SELECT NUM6 FROM Tbl6
)X

Output:

AvgNum
31

Niladri Biswas