开发者

help with query in DB2

i would like your help with my query.I h开发者_JS百科ave a table employee.details with the following columns: branch_name, firstname,lastname, age_float.

I want this query to list all the distinct values of the age_float attribute, one in each row of the result table, and beside each in the second field show the number of people in the details table who had ages less than or equal to that value. Any ideas? Thank you!


You can use OLAP functions:

SELECT DISTINCT age_float, 
       COUNT(lastname) OVER(ORDER BY age_float) AS number
  FROM employee_details

COUNT(lastname) OVER(ORDER BY age_float) AS number orders rows by age, and returns employees count whose age <= current row age

or a simple join:

SELECT A.age_float, count(lastname)
  FROM (SELECT DISTINCT age_float FROM employee_details) A
JOIN employee_details AS ED ON ED.age_float <= A.age_float
GROUP BY A.age_float
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜