Sub query - calculating totals, sub-totals by group

[sql14]

I am having problems with the following sub query calculation. What I would like to achieve is grand totals for all records, sub-totals by employee, then subtotals by shipping method. I can get the first two to work as expected, but the last subtotal by shipping method (TOT_FREIGHT) is not working correctly. I am using the northwind database based on the Orders table.

This is the output I would like to achieve:

Employee Ship Via Freight TOT_FREIGHT TOT_EMP TOT_ALL
Buchanan Fed Ex $12 $25 $175 $1500
Buchanan Fec Ex $13 $25 $175 $1500

This is the SQL:

SELECT Orders.EmployeeID, Orders.ShipVia, Orders.Freight, (SELECT SUM([Orders.Freight])
FROM Orders As T2
WHERE T2.[EmployeeID] = Orders.[EmployeeID] AND T2.[ShipVia] = Orders.[ShipVia]) AS TOT_FREIGHT,

(SELECT SUM([Freight]) FROM Orders AS T2 WHERE T2.[EmployeeID] = orders.[EmployeeID]) AS TOT_EMP,

(SELECT SUM([Freight]) FROM Orders) AS TOT_ALL

FROM Orders
GROUP BY Orders.EmployeeID, Orders.ShipVia, Orders.Freight
HAVING (((Orders.EmployeeID)=5) AND ((Orders.ShipVia)=3));

Thanks.

 

[rac2]

Access has reporting features that might provide the grand total and subtotals on each line. I cannot help with that but it would be something to look at.

From the SQL point-of-view, I might approach this with the use of several independent queries that obtain the subtotals and JOIN them for the desired result.

Starting with the grand total, saved under the name GrandTotal-

SELECT SUM([Freight]) FROM Orders) AS TOT_ALL

Then the breakdown or subtotals by employee, shipping, and freight saved as EmployeeShippingFreightTotals-

SELECT Orders.EmployeeID,
       Orders.ShipVia, 
       Orders.Freight,
       SUM([Orders.Freight]) AS FREIGHT
FROM Orders

GROUP BY Orders.EmployeeID, Orders.ShipVia, Orders.Freight

And the subtotals by employee and shipping saved as EmployeeShippingTotals-

SELECT Orders.EmployeeID,
       Orders.ShipVia, 
       SUM([Orders.Freight]) AS FREIGHT 
FROM Orders

GROUP BY Orders.EmployeeID, Orders.ShipVia

Then JOIN these as follows-

SELECT a.EmployeeID,
       a.ShipVia, 
       a.Freight,
       b.FREIGHT AS TOT_FREIGHT,
       c.FREIGHT AS TOT_EMP,
       d.TOT_ALL

FROM GrandTotal d,
     EmployeeShippingFreightTotals a
JOIN EmployeeTotals b ON b.EmployeeID = a.EmployeeID
JOIN EmployeeShippingTotals b ON b.EmployeeID = a.EmployeeID
                      AND b.ShipVia = a.ShipVia

WHERE a.EmployeeID=5 AND a.ShipVia=3;

I didnt give the query for EmployeeTotals but it is straightforward, GROUP BY EmployeeID.

Let me know whether this works for you.

 

[sql14]

I was hoping to try and accomplish the totals for the ship via within the same query, but it seems its not possible. I will try your solution though, thanks.

 

[sql14]

Finally, sucess. Thanks to thread

http://www.tek-tips.com/viewthread.cfm?qid=60632,

located under the ANSI_SQL forum. I was able to figure this out with one query. The only issue I found, is that when the query is saved in access, the access software seems to put brackets [] around the subquery statements. It runs just fine, but when you try to make changes you get an error indicating: "Invalid bracketing of name". What I have done is just change the brackets to parens () and you can make any changes you like.

Here is the code:
SELECT A.EmployeeID, A.ShipVia, A.FRE_DET, B.FRE_TOT, C.EMP_TOT, D.GRND_TOT
FROM (SELECT EmployeeID, ShipVia, Freight, SUM([Freight]) AS FRE_DET FROM Orders
GROUP BY EmployeeID, ShipVia, Freight) AS A,(SELECT EmployeeID, ShipVia, SUM([Freight]) AS FRE_TOT FROM Orders GROUP BY EmployeeID, ShipVia) AS B,
(SELECT EmployeeID, SUM([Freight]) AS EMP_TOT FROM Orders GROUP BY EmployeeID) AS C, (SELECT SUM([Freight]) AS GRND_TOT FROM Orders) AS D
WHERE (((A.EmployeeID)=.[EmployeeID] And (A.EmployeeID)=[C].[EmployeeID]) AND ((A.ShipVia)=.[ShipVia]));

Gives the output requested above, all in one query.

 

[rac2]

Good job finding that thread and posting it. The one statement solution is nice to have.

The brackets and parentheses provided by Access are redundant for the most part. Brackets are essential with column names that contain spaces and are nice to differentiate keywords which have been used as column names. Both are bad practices, but an automatic code generator must assume that every column name could be problematic therefore it must use brackets around every name. Parentheses are required syntax in a few SQL clauses and necessary to indicate groupings as well. A common syntactical use is with subqueries which must be contained in parens.

I find the automatic code generated by Access is difficult to read due to extraneous [] and () and lack of indenting. Here is your query with the necessary [] and (). But it doesnt matter what you type or paste into Access SQL query design, it will junk it up for you when you save.

SELECT 
       A.EmployeeID,
       A.ShipVia,
       A.FRE_DET,
       B.FRE_TOT,
       C.EMP_TOT,
       D.GRND_TOT
FROM
     ( SELECT EmployeeID,
              ShipVia,
              Freight,
              SUM(Freight) AS FRE_DET
       FROM Orders
       GROUP BY EmployeeID, ShipVia, Freight
     ) AS A,

     ( SELECT EmployeeID,
              ShipVia,
              SUM( Freight ) AS FRE_TOT
       FROM Orders
       GROUP BY EmployeeID, ShipVia
     ) AS B, 

      ( SELECT EmployeeID,
        SUM(Freight) AS EMP_TOT
        FROM Orders
        GROUP BY EmployeeID
      ) AS C,

      ( SELECT SUM(Freight) AS GRND_TOT
        FROM Orders
      ) AS D
WHERE
      A.EmployeeID = B.EmployeeID
      AND
      A.EmployeeID = C.EmployeeID
      AND
      A.ShipVia = B.ShipVia
;

The WHERE clause is an alternative syntax for the JOIN conditions.

SELECT 
       A.EmployeeID,
       A.ShipVia,
       A.FRE_DET,
       B.FRE_TOT,
       C.EMP_TOT,
       D.GRND_TOT
FROM
     ( SELECT EmployeeID,
              ShipVia,
              Freight,
              SUM(Freight) AS FRE_DET
       FROM Orders
       GROUP BY EmployeeID, ShipVia, Freight
     ) AS A

JOIN
     ( SELECT EmployeeID,
              ShipVia,
              SUM( Freight ) AS FRE_TOT
       FROM Orders
       GROUP BY EmployeeID, ShipVia
     ) AS B ON A.EmployeeID = B.EmployeeID
              AND A.ShipVia = B.ShipVia
JOIN
     ( SELECT EmployeeID,
        SUM(Freight) AS EMP_TOT
        FROM Orders
        GROUP BY EmployeeID
     ) AS C ON A.EmployeeID = C.EmployeeID,

      ( SELECT SUM(Freight) AS GRND_TOT
        FROM Orders ) AS D
;

Here we can see that the subqueries appear in place of the VIEWs, or queries saved by name, that I presented. Either approach works and they are equally efficient, I believe. I prefer using VIEWs for two reasons, readability, and re-use. Possibly the grand total and employee totals pieces will be useful in other contexts.

Regards.