Question: I have been working with databases on access but for the first time i have to use MS SQL server 2012, hence, i have to write my queries as i find the query builder not as user friendly as access.

The problem i am stuck on is not a very challenging one. I have 2 tables as follows:

* `dbo.WF_PM_ACCT_DET_DB with columns to use Account, Postal_Code, Client_Birth_Date`
* `dbo.WF_PM_SUM_DB with columns to use Account, Total_Avg_AuA`

What i want to do is have a query with results that groups by postal code, counts the number of client in the respective postal code, also groups by age where AuA is > 0

So first i need to group client in 3 groups by age (<35,35-55 and >55). Then count how many clients are in a particular postal code in that certain age group. Also select only clients that have a sum(AuA)>0 from the second table. the output would look something like this

```
Postal code   CountofClient Age_group
1m1 2m2          50            <35
1m1 2m2          20           35-50
and so on...

```

so far this is what i have:

```
SELECT        Postal_Code, COUNT(Account) AS Count_Account, Age,                 
AVG(YEAR(GETDATE()) - YEAR(client_birth_date)) AS 'Age'

CASE WHEN AGE <=35                       THEN 'AGEGROUPA'
       WHEN AGE >35 AND AGE <55          THEN 'AGEGROUPB'
       WHEN AGE >=55                     THEN 'AGEGROUPC'
end as 'clientageseg'

FROM            dbo.WF_PM_ACCT_DET_DB
GROUP BY Postal_Code, Client_Birth_Date, Age

```

any help will be appreciated. Thanks.

Answer: 
```
;WITH cte AS (
    SELECT
       postal_code
       ,Account
       ,CASE
          WHEN
             (DATEADD(year,DATEDIFF(year, [DOB]  ,GETDATE()) , [DOB]) > GETDATE())
          THEN DATEDIFF(year, [DOB]  ,GETDATE()) -1
          ELSE DATEDIFF(year, [DOB]  ,GETDATE())
       END as Age
   ,SUM(s.AuA) OVER (PARTITION BY a.Account) as AuASum
    FROM
       dbo.WF_PM_ACCT_DET_DB a
       INNER JOIN dbo.WF_PM_SUM_DB s
   ON a.Account = s.Account
)

SELECT
    postal_code
    ,COUNT(Account) as Count_Account
    ,CASE
       WHEN Age <= 35 THEN 'AGEGROUPA'
       WHEN Age > 35 AND Age < 55 THEN 'AGEGROUPB'
       WHEN Age >= 55 THEN 'AGEGROUPC'
    END as ClientAgeSeg
FROM
    cte
WHERE
   AuASum > 0
GROUP BY
    postal_code
    ,CASE
          WHEN Age <= 35 THEN 'AGEGROUPA'
          WHEN Age > 35 AND Age < 55 THEN 'AGEGROUPB'
          WHEN Age >= 55 THEN 'AGEGROUPC'
   END

```

Welcome to SQL Server from Acess! In the end you will love it, so much more you can do. When beginnign some of the nice features to dig into are *Common Table Expressions [CTE]* and *Window Functions*. I am using a CTE in the above example. You don't have to but it will make your life a little easier when you get to the date comparison.

Note when calculating AGE you need to determine if the birthdate has already occurred in the year or is still to come.
**Try this:**

```
SELECT  DISTINCT  Postal_Code,
        COUNT(Account)OVER (PARTITION BY Postal_Code) AS Count_Account,
        Age,                 
        AVG(YEAR(GETDATE()) - YEAR(client_birth_date)) AS 'Age',
        CASE WHEN AGE <=35                       THEN 'AGEGROUPA'
             WHEN AGE >35 AND AGE <55          THEN 'AGEGROUPB'
             WHEN AGE >=55                     THEN 'AGEGROUPC'
        END as 'clientageseg'
FROM dbo.WF_PM_ACCT_DET_DB A
LEFT JOIN dbo.WF_PM_SUM S ON A.Account=S.AccountNumber
WHERE S.AccountNumber IS NOT NULL

```