-2

I'm trying to make SQL return how many users are inside each age bracket grabbing the date of birth from the user from the table GUEST and converting it into years, at the moment I'm trying to make it work with a stored procedure that looks like this for now:

CREATE PROCEDURE usp_AgeBracket
AS
BEGIN
    
    SELECT SUM(CASE WHEN (SELECT DATEDIFF(YY,GUEST.DOB,GETDATE()) FROM GUEST) BETWEEN 18 AND 24 THEN 1 ELSE 0 END) AS [18-24],
        SUM(CASE WHEN (SELECT DATEDIFF(YY,GUEST.DOB,GETDATE()) FROM GUEST) BETWEEN 25 AND 34 THEN 1 ELSE 0 END) AS [25-34],
        SUM(CASE WHEN (SELECT DATEDIFF(YY,GUEST.DOB,GETDATE()) FROM GUEST) > 34 THEN 1 ELSE 0 END) AS [34+]
    FROM GUEST
END
GO

but this returns me MSG 130 error and I don't know how to solve it with my current knowledge.

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

0

2 Answers 2

1

Assuming GUEST.DOB is a date-based column, you should be able to do something like this:

SELECT 
    SUM(IIF(DATEDIFF(YY,GUEST.DOB,GETDATE()) BETWEEN 18 AND 24, 1, 0)) AS [18-24],
    SUM(IIF(DATEDIFF(YY,GUEST.DOB,GETDATE()) BETWEEN 25 AND 34, 1, 0)) AS [25-34],
    SUM(IIF(DATEDIFF(YY,GUEST.DOB,GETDATE()) > 34, 1, 0)) AS [35+],
FROM GUEST

No need to have subqueries in your SELECT clause, like your original query. This is just a set of total table aggregate expressions instead.

3
  • 1
    That's still not a correct way to calculate age.
    – Paul White
    Jan 12 at 16:05
  • @PaulWhite As far as the granularity of only calculating by year / rounding errors essentially? Unfortunately not able to test at the moment.
    – J.D.
    Jan 12 at 18:50
  • 2
    It's just hopelessly wrong, I'm afraid. You can find a collection of right and wrong answers at stackoverflow.com/q/1572110
    – Paul White
    Jan 12 at 19:41
-1

After some more thoughts I arrived to this solution that worked for my case:

SELECT SUM(CASE WHEN DATEDIFF(YY, GUEST.DOB, GETDATE()) BETWEEN 18 AND 24 THEN 1 ELSE 0 END) AS [18-24],
        SUM(CASE WHEN DATEDIFF(YY, GUEST.DOB, GETDATE()) BETWEEN 25 AND 34 THEN 1 ELSE 0 END) AS [25-34],
        SUM(CASE WHEN DATEDIFF(YY, GUEST.DOB, GETDATE()) > 34 THEN 1 ELSE 0 END) AS [34+]
    FROM GUEST

Hope this helps someone in the future aswell

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.