monika.m.srivastava
BAN USERI have implemented the sum function using recursive with clause in Oracle SQL, similarly it can be modified for average (sum/count)
WITH s (iteration, num_sum)
AS (SELECT row_id iteration, xn.num
FROM ( SELECT NUM,
ROW_NUMBER () OVER (ORDER BY num) row_id
FROM xxtest_num) xn
WHERE xn.row_id = 1 -- first row in the table
UNION ALL
SELECT s1.iteration + 1, s1.num_sum + xn1.num
FROM s s1,
( SELECT NUM,
ROW_NUMBER () OVER (ORDER BY num) row_id
FROM xxtest_num) xn1
WHERE s1.iteration <= 10 -- stopping condition count of rows
AND s1.iteration + 1 = xn1.row_id)
SELECT num_sum
FROM ( SELECT *
FROM s s1
ORDER BY num_sum DESC)
WHERE ROWNUM = 1;
Oracle SQL code
SELECT candidateid, name
from(
SELECT v.candidateid, c.name , count(v.id) num_of_votes
FROM candidate c, vote v
WHERE c.id = v.candidateid
Group by v.candidateid
Order by count(v.id) desc)
where rownum = 1
;
table : users
columns: id, key, value
select id
from users u
where key = 'name'
and value like 'H%'
INTERSECT
select id
from users u
where key = age
and age between 22 and 35
- monika.m.srivastava June 15, 2016