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
精彩评论