SUMIF in a SQL query-CodePudding

I wrote a query which aggregates some values from a table but I would like to add another column to this query which does another aggregation, just like SUMIF in Excel.

So, as in the below example, I would like to add column SUMMV which would work the same as =SUMIF(A:A,A1,B:B).

Please keep in mind that in my query column MV is an aggregation of some other column.

NAME	MV	SumMV
X	2	5
Y	1	6
Z	3	4
X	3	5
Z	1	4
Y	5	6

My query looks like below:

Select E.Name, P.ID, P.Key, P.Name,P.type, P.MV, , P.Exp as TNA, FROM tblposition P INNER JOIN QScope ON E P.ID=E.ID WHERE Type NOT IN (2,5,10,15)

UNION ALL Select E.Name, P.ID, P.Key, P.Name,P.Type, sum(P.MV) AS MV, , P.Exp as TNA, FROM tblposition P INNER JOIN QScope ON E P.ID=E.ID WHERE Type IN (2,5,10,15) AND P.MVT > -0.01 GROUP BY P.Key,P.ID,P.Name,P.Type,P.Exp
ORDER BY Name, Type

CodePudding user response：

Options in Access:

Build a query that aggregates by ID then join that query to the first query
Calculate field with DSum() domain aggregate function - however, this can cause slow performance in queries
DSum("Value", "table or query name", "ID=" & [ID]) AS SumValue
Build a VBA custom function and call it in the first query - this function would either open a recordset object or execute DSum()

CodePudding user response：

There are no Windowing functions in AccessSQL but you can use a subquery either on the SELECT clause, or in the FROM part. Something like this:

Select 
     E.Name
   , P.MV
   , (select sum(P2.MV)
      FROM tblposition P2 
           
           INNER JOIN 
           QScope e2 ON P2.ID=E2.ID 
      WHERE P2.Type NOT IN (2,5,10,15)
      AND p1.Name=P.Name
     ) as SumMV
 
FROM tblposition P 

     INNER JOIN 
     QScope ON E P.ID=E.ID 
WHERE P.Type NOT IN (2,5,10,15)

should work, but I can't say it is correct because you haven't given us the table structures or sample data from each table.