SQL sum after group by

I have a query that I built for Volusion, which is very limited in SQL abilities, such as I can't use temp tables, or even variables... which is how I would normally try to do this task.

but, I have this query which gives me the info I need, but I have to work with it in Excel after running... and I'd like to get the query to get me the results I need.

What I ultimately need is the cost/profit by google_gender and by rep... summed. Is there a way I can accomplish this without variables or temp tables?

here's my current query:

SELECT
    Products_Joined.Google_Gender,
    REPLACE(REPLACE(REPLACE(REPLACE(Customers.SalesRep_CustomerID,379646,'Dan'),658024,'Owen'),643352,'Mary'),584310,'Pete') as SalesRep,
    SUM(OrderDetails.ProductPrice)*OrderDetails.Quantity as TotalSold,
    SUM(OrderDetails.Vendor_Price)*OrderDetails.Quantity as VendorCost,
    SUM(OrderDetails.ProductPrice)*OrderDetails.Quantity-(OrderDetails.Vendor_Price)*OrderDetails.Quantity as Profit
FROM
    Products_Joined, OrderDetails, Orders, Customers
WHERE
    Products_Joined.ProductCode=OrderDetails.ProductCode 
    AND OrderDetails.OrderID=Orders.OrderID 
    AND Orders.CustomerID=Customers.CustomerID
    AND Orders.OrderDate BETWEEN '05/01/16 00:00' and '05/31/16 23:59'
    AND Customers.SalesRep_CustomerID in ('379646' , '658024' , '643352' , '584310')
    AND Orders.OrderStatus <> 'Cancelled'
GROUP BY    
    Products_Joined.Google_Gender,
    OrderDetails.Quantity,
    OrderDetails.Vendor_Price,
    OrderDetails.ProductPrice,
    Customers.SalesRep_CustomerID

because the query has to pull item information from OrderDetails, to get the "sold price" it lines out each order# and sku in the resulting file, which i don't need, I just need the total amount by each google_gender, by rep. and there's only going to be the 4 reps :)

Solution

To get the SUM per gender/rep you need to get rid of the other grouping levels. Without any schema or data I'm just guessing, but this might give you what you want;

SELECT
    Products_Joined.Google_Gender,
    REPLACE(REPLACE(REPLACE(REPLACE(Customers.SalesRep_CustomerID,379646,'Dan'),658024,'Owen'),643352,'Mary'),584310,'Pete') as SalesRep,
    SUM(OrderDetails.ProductPrice*OrderDetails.Quantity) as TotalSold,
    SUM(OrderDetails.Vendor_Price*OrderDetails.Quantity) as VendorCost,
    SUM(OrderDetails.ProductPrice*OrderDetails.Quantity)-SUM(OrderDetails.Vendor_Price*OrderDetails.Quantity) as Profit
FROM
    Products_Joined, OrderDetails, Orders, Customers
WHERE
    Products_Joined.ProductCode=OrderDetails.ProductCode 
    AND OrderDetails.OrderID=Orders.OrderID 
    AND Orders.CustomerID=Customers.CustomerID
    AND Orders.OrderDate BETWEEN '05/01/16 00:00' and '05/31/16 23:59'
    AND Customers.SalesRep_CustomerID in ('379646' , '658024' , '643352' , '584310')
    AND Orders.OrderStatus <> 'Cancelled'
GROUP BY    
    Products_Joined.Google_Gender,
    Customers.SalesRep_CustomerID

Note that all the OrderDetails are inside the SUM functions.