I have a table with registered users, in开发者_开发百科 which i save year as varchar values simply because i take just an year. I want to create pie chart with ages to show which users are more likely to register.
Query below gives me count of user ages which appear more than 5 times in Table to avoid small results. While these small results, below "having ount(userID)>5" i want to appear as others. What should i add to this query or possibly to re-design it. I can create silly solutions like to take all years that appear in initial query and then select all besides those year but there must be better and more creative way of writing this query.
So result will be something like that 1 10 1990 2 4 1980 3 10 others
select count(userID) ageCount,userBirthYear from Users
group by userBirthYear
having count(userID)>5
order by count(userID) desc
thanks
Here's a regrouping solution (union-less) to avoid repeated IO.
The basic idea is you want every record to contribute to the result, so there should be no WHERE or HAVING clauses.
SELECT
SUM(sub.ageCount) as ageCount,
sub.userBirthYear
FROM
(
SELECT Count(userId) ageCount,
CASE WHEN COUNT(userID) > 5)
THEN userBirthYear
ELSE 'Other'
END as userBirthYear
FROM Users
GROUP BY userBirthYear
) as sub
GROUP BY sub.userBirthYear
Here's one way (assuming SQL2005 or later).
With Ages As
(
select count(userID) ageCount,userBirthYear
from Users
group by userBirthYear
)
SELECT ageCount,userBirthYear FROM Ages WHERE ageCount>5
UNION ALL
SELECT sum(ageCount) ,'others' As userBirthYear FROM Ages WHERE ageCount<=5
select count(userID) ageCount,userBirthYear from Users
group by userBirthYear
having count(userID)>5
union
SELECT SUM(agecount), 'Others'
FROM (select count(userID) ageCount,'Others' userBirthYear from Users
group by userBirthYear
having count(userID)<5)TMP
order by count(userID) desc
精彩评论