SUM of the SUMs in MS Access SQL

Question

Dears, I have a question in MS Access SQL. I have 4 tables.

After Union All and SUMs, to this point I have all the COUNTs of the number of the SUMs. Now I need the SUM of all SUMs by row and by columns. Thank you for your help.

Current result:

 ID Name  5/1/2018 5/2/2018 5/3/2018 Count
-- ----- -------- -------- -------- -----
1  Susan       20       30       45     3
2  Juan        15       70              2
3  Tracy       50       60       40     3
4  Jenny       60       8        60     3
5  Bill                         100     1

Expected Result:

ID Name  5/1/2018 5/2/2018 5/3/2018 Count E_Total
-- ----- -------- -------- -------- ----- -------
1  Susan       20       30       45     3    95
2  Juan        15       70              2    85
3  Tracy       50       60       40     3   150
4  Jenny       60       8        60     3   128
5  Bill                         100     1   100
D_Total       145       168     245         558

Current Query:

select es.EmpID, es.FirstName, 
   sum(switch(es.DateS = #5/1/2018#, es.Amount)) AS [5/1/2018], 
   sum(switch(es.DateS = #5/2/2018#, es.Amount)) AS [5/2/2018],
   sum(switch(es.DateS = #5/3/2018#, es.Amount)) AS [5/3/2018],
   (max(iif(es.DateS = #5/1/2018#, 1, 0)) +
    max(iif(es.DateS = #5/2/2018#, 1, 0)) +
    max(iif(es.DateS = #5/3/2018#, 1, 0))
   ) as num_dates      
from (
select e.EmpID, e.FirstName, s.DateS, s.Amount 
      from Employee as e inner join
           Sale as s on ( s.EmployeeID = e.EmpID AND s.Amount IS NOT NULL)
      where s.DateS between #5/1/2018# and #5/3/2018#
      union all
      select e1.EmpID, e1.FirstName, s1.DateS, s1.Amount 
      from Employee1 as e1 inner join
           Sale1 as s1 on ( s1.EmployeeID = e1.EmpID and s1.Amount IS NOT NULL)
      where s1.DateS between #5/1/2018# and #5/3/2018#
     ) as es
group by es.EmpID, es.FirstName 
order by es.EmpID;

I have the tables and the query pictured here.
As in the picture, The column "E_Total" and row "D_Total are what I need they look like. Or, should I stop here and program the DataGridView in VB.NET to do the job? Thank you very much for your helps or suggestions.

Please share the query as text, so we can modify it, not as a photo. Also, if this is homework, say so, and read [How do I ask and answer homework questions?](https://meta.stackoverflow.com/q/334822) — Erik A, May 27 '18 at 07:49
To add some background, this is Tony's second question, which follows up on his first question. Which can be found [here](https://stackoverflow.com/questions/50439876/). Since the best solution would be MS Access specific it was suggested to post a new one with the MS Access tag. — LukStorms, May 27 '18 at 09:59
@LukStorms Thanks for the background. I always get a bit worried when I see photos and no attempt, because I don't want to do others homework and disturb their learning process. But I guess he did really make these photos himself (and I will remain clueless as to why they aren't screenshots), and why he hasn't shared the code as code himself. Kudos to the amount of effort you put in that previous answer. — Erik A, May 27 '18 at 10:56
Hi Erik and LukStorm, this is not a homework. It is part of my own vb.net project for my business. This is my part of pulling the works of my employees to calculate their commissions. I had older version of this program in VB6. Now I want to improve to VB.NET with a better SQL. So rest assure that it is not for school. Thank you for your times, helps, and concerns. I am new of posting questions like this so I have no clue of how to posting question. I saw that you can indent the works or question so neat but I could not even I tried so hard. Sorry for that. — Tony Nguyen, May 27 '18 at 22:22
I learned SQL 18 yrs ago in college, my old vb6 program was 12 yrs old. I had to use Select statement for each day and put them in the datagrid table, then add them up individual day. That is one of my reason for why I need to redo the program. Thank you LukStorm was very good and patience to help me out. — Tony Nguyen, May 27 '18 at 22:32

score 1 · Answer 1 · answered May 27 '18 at 10:51

You're essentially counting which columns are not null. Because you're counting within a row, the easiest way to do that is just using IIF statements:

SELECT DISTINCT EmpID, FirstName, 
 Sum(Switch(q.DateS = #5/1/2018#, q.Amount)) AS [5/1/2018], 
 Sum(Switch(q.DateS = #5/2/2018#, q.Amount)) AS [5/2/2018],
 Sum(Switch(q.DateS = #5/3/2018#, q.Amount)) AS [5/3/2018]
 Iif(Sum(Switch(q.DateS = #5/1/2018#, q.Amount)) Is Not Null, 1, 0) +
 Iif(Sum(Switch(q.DateS = #5/2/2018#, q.Amount)) Is Not Null, 1, 0) +
 Iif(Sum(Switch(q.DateS = #5/3/2018#, q.Amount)) Is Not Null, 1, 0) As [Count]
FROM 
( 
    SELECT u1.EmpID, u1.FirstName, a1.DateS, a1.Amount 
    FROM Employee AS u1 
    INNER JOIN Sale AS a1 
       ON (a1.EmployeeID = u1.EmpID AND a1.Amount IS NOT NULL) 
    WHERE a1.DateS BETWEEN #5/1/2018# AND #5/3/2018#

    UNION ALL 

    SELECT u2.EmpID, u2.FirstName, a2.DateS, a2.Amount 
    FROM Employee1 AS u2 
    INNER JOIN Sale1 a2 ON (a2.EmployeeID = u2.EmpID AND a2.Amount IS NOT NULL) 
    WHERE a2.DateS BETWEEN #5/1/2018# AND #5/3/2018#

) AS q 
GROUP BY q.EmpID, q.FirstName 
ORDER BY q.EmpID;

Note that sometimes, you can refer to column names when doing calculations with other calculated columns, e.g. Iif([5/1/2018] Is Not Null. I don't know the specifics of when that is and isn't allowed in Access, so I tend to avoid it.

Ah I see. Well, in another database I would probably do something as in `count(case when q.Date between ... and ... then q.Amount end)`. Since a count on a value doesn't count the NULL's. And wouldn't know if that Switch function can return nulls. But at a glance, this should work. I had suggested to consider a PIVOT, but I'm guessing that's probably to tricky with those date column names. — LukStorms, May 27 '18 at 11:58
I mainly chose this approach because Access should be able to optimize that count to a row-level calculation (it doesn't need to count or sum anything additional from the tables, because all those `SUM` things are already calculated). If those weren't already calculated, I might've gone your way. It's hard to say exactly how smart the optimizer is in Access since I can't see execution plans without weird trickery, but I believe it can optimize this well. — Erik A, May 27 '18 at 12:03
My next question is: can I use more SQL in my current SQL to sum all the SUMs by employees and by dates? It mean by row and by columns? Thank you — Tony Nguyen, May 27 '18 at 23:16

score 1 · Answer 2 · edited May 28 '18 at 01:06

In another database you would use count(distinct). That is not an option in MS Access.

I would write this as:

select es.EmpID, es.FirstName, 
   sum(switch(es.DateS = #5/1/2018#, es.Amount)) AS [5/1/2018], 
   sum(switch(es.DateS = #5/2/2018#, es.Amount)) AS [5/2/2018],
   sum(switch(es.DateS = #5/3/2018#, es.Amount)) AS [5/3/2018],
   (max(iif(es.DateS = #5/1/2018#, 1, 0)) +
    max(iif(es.DateS = #5/2/2018#, 1, 0)) +
    max(iif(es.DateS = #5/3/2018#, 1, 0))
   ) as num_dates      
from (
  select e.EmpID, e.FirstName, s.DateS, s.Amount 
  from Employee as e inner join
       Sale as s on (s.EmployeeID = e.EmpID AND s.Amount IS NOT NULL)
  where s.DateS between #5/1/2018# and #5/3/2018#
  union all
  select e1.EmpID, e1.FirstName, s1.DateS, s1.Amount 
  from Employee1 as e1 inner join
       Sale1 as s1 on (s1.EmployeeID = e1.EmpID and s1.Amount IS NOT NULL)
  where s1.DateS between #5/1/2018# and #5/3/2018#
 ) as es
group by es.EmpID, es.FirstName 
order by es.EmpID;

Notes:

select distinct is almost never used with group by. You certainly don't need it in this case.
When you give tables aliases, use table abbreviations for the alias. It makes the query much easier to follow.
Qualify all column names, not just some of them.

You're missing closing parentheses for all your `Max` statements there (you only have one where you need two to close the `Max` and the `Iif`), causing a syntax error. Also, as said on my answer in a comment, reusing aggregates sometimes allows an execution to run faster, since it can be calculated based on other columns in the result set, and doesn't need to be separately calculated. — Erik A, May 27 '18 at 12:57

SUM of the SUMs in MS Access SQL

2 Answers2