convert access group by query to sql server query

I’m unable to convert MS Access query to SQL SERVER Query, with changing the group by columns because it will effect in the final result. The purpose of this query is to calculate the Creditor and debtor of accounts of projects.

I tried rewriting with ‘CTE’ but couldn’t get any good result.. I hope someone could help me.. Thanks in advance…

this is the query I want to convert:

SELECT Sum(ZABC.M) AS M, Sum(ZABC.D) AS D, ZABC.ACC_NUMBER, ZABC.PROJECT_NUMBER, [M]-[D] AS RM, [D]- 
[M] AS RD
FROM ZABC
GROUP BY ZABC.ACC_NUMBER, ZABC.PROJECT_NUMBER
ORDER BY ZABC.PROJECT_NUMBER;

Answer

The problem with the query are [M] and [D] in the select clause: these columns should either be repeated in the group by clause, or surrounded by an aggregate function. Your current group by clause gives you one row per (acc_number, project_number) tuple: you need to choose which computation you want for D and M, that may have several different values per group.

You did not explain the purpose of the original query. Maybe you meant:

SELECT 
    Sum(ZABC.M) AS M, 
    Sum(ZABC.D) AS D, 
    ZABC.ACC_NUMBER, 
    ZABC.PROJECT_NUMBER, 
    Sum(ZABC.M) - SUM(ZABC.D) AS RM, 
    SUM(ZABC.D) - SUM(ZABC.M) AS RD
FROM ZABC
GROUP BY ZABC.ACC_NUMBER, ZABC.PROJECT_NUMBER
ORDER BY ZABC.PROJECT_NUMBER;

There is a vast variety of aggregate functions available for you to pick from, such as MIN(), MAX(), AVG(), and so on.