SQL fetch records using group by with 3 conditions

Question

I'm trying to write a query which gives me the number of patient visits by age, gender and condition(Diabetes, Hypertension etc). Get the visit count for patients having diabetes and group by gender and patients who fall between the age range of 45-54. I used Inner Join to get only the rows which are present in both tables. I get the error:

age.Age is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Do you think I should use partition by age.age?

TABLE_A

+------------+------------+------------+
| Member_Key | VisitCount |    date    |
+------------+------------+------------+
|       4000 |          1 | 2014-05-07 |
|       4000 |          1 | 2014-05-09 |
|       4001 |          2 | 2014-05-08 |
+------------+------------+------------+

TABLE_B

+------------+--------------+
| Member_Key |  Condition   |
+------------+--------------+
|       4000 | Diabetes     |
|       4000 | Diabetes     |
|       4001 | Hypertension |
+------------+--------------+

TABLE_C

+------------+---------------+------------+
| Member_Key | Member_Gender | Member_DOB |
+------------+---------------+------------+
|       4000 | M             | 1970-05-21 |
|       4001 | F             | 1968-02-19 |
+------------+---------------+------------+

Query

SELECT c.conditions,
       age.gender,
       CASE
           WHEN age.age BETWEEN 45 AND 54
           THEN SUM(act.visitcount)
       END AS age_45_54_years
FROM table_a act
     INNER JOIN
(
    SELECT DISTINCT
           member_key,
           conditions
    FROM table_b
) c ON c.member_key = act.member_key
     INNER JOIN
(
    SELECT DISTINCT
           member_key,
           member_gender,
           DATEPART(year, '2017-10-16')-DATEPART(year, member_dob) AS Age
    FROM [table_c]
) AS age ON age.member_key = c.member_key
GROUP BY c.conditions,
         age.member_gender;

Expected Output

+--------------+--------+-------------+
|  Condition   | Gender | TotalVisits |
+--------------+--------+-------------+
| Diabetes     | M      |           2 |
| Hypertension | F      |           2 |
+--------------+--------+-------------+

Show us db schema, sample data, current and expected output. Please read [**How-to-Ask**](http://stackoverflow.com/help/how-to-ask) — Juan Carlos Oropeza, Oct 17 '17 at 14:42
You do realize that your age calculation is not accurate? It works if the persons birthday has already happened this year. — Sean Lange, Oct 17 '17 at 14:49
@SeanLange Now Im confused too :( what you mean and how we improve the answer? — Juan Carlos Oropeza, Oct 17 '17 at 15:11
@JuanCarlosOropeza DATEDIFF counts the number of thresholds passed between 2 dates. Try using either the OPs logic or the datediff logic with a date from January and a date from December for the birthdate. It will return the same number. You have to subtract 1 from the match if the same date has not yet occurred in the current year. — Sean Lange, Oct 17 '17 at 15:16
@SeanLange Is right. After searching this site result the age calculation isnt that simple. But here is one [answer](https://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate) with very detail solution. — Juan Carlos Oropeza, Oct 17 '17 at 15:53
@SeanLange But my answer EDIT using `DATEADD` dont have that problem, isnt? because that actually use the date 45 and 50 years ago. — Juan Carlos Oropeza, Oct 17 '17 at 16:03
Yes I agree that your edited answer is working correctly. :) — Sean Lange, Oct 17 '17 at 16:06

Juan Carlos Oropeza · Answer 1 · 2017-10-17T16:08:43.460

You can simplify your query filtering the age on the WHERE condition

And as Sean Lange said, use DATEDADD and GETDATE() to calculate the age more accurately.

SQL DEMO

SELECT [Condition], 
       [Member_Gender] as [Gender], 
       SUM([VisitCount]) as [VisitCount]
FROM TableA A
JOIN (SELECT DISTINCT [Member_Key], [Condition] 
      FROM TableB) B 
  ON A.[Member_Key] = B.[Member_Key]
JOIN TableC C 
  ON A.[Member_Key] = C.[Member_Key]
WHERE [Member_DOB] BETWEEN DATEADD(year, -50 , GETDATE())
                       AND DATEADD(year, -45 , GETDATE())
GROUP BY [Condition], [Member_Gender]

EDIT

Have to change the WHERE condition to solve the age precision and allow index use.

SQL fetch records using group by with 3 conditions

1 Answers1