CODEWITHSUNDEEP
sql
Austin
Austin

Reputation: 13

Combine Total costs

Fairly new to SQL and attempting to build a query which is "supposed to" grab all clients and combine their transactions and shipping QTY. Unfortunatly I am not able to find a way to just SUM the data into one row per client.

Use Test
set NOCOUNT ON

select CompanyName, (SUM(t.Quantity) * b.Cost) AS TotalCost,
SUM(t.Quantity) AS Qty
 FROM
    tblSFCOrder o
     join tblSFCClient c on o.ClientNumber = c.ClientNumber
    JOIN    tblSFCOrderTxn t ON o.OrderID = t.OrderID
    JOIN    tblSFCInventory i ON t.SKU = i.SKU
            AND o.ClientNumber = i.ClientNumber
     JOIN   tblSFCBox b ON i.BoxID = b.Id 
     JOIN   tblSFCShipment s ON o.OrderID = s.OrderID 

where o.StatusID = 4 and o.ClientNumber = 1486
and shipdate between    '2015-10-01 00:40:01.370' AND '2015-10-31        23:23:38.163'
group by
CompanyName, b.Cost

and the end result is

1.CompanyName   TotalCost   Qty
2.Client1        191.60     479
3.Client1        159.12     306
4.Client1        2.64       4

And I want is just 

 1.CompanyName  TotalCost   Qty
 2. Client1      353.36     789

Upvotes: 1

Views: 96

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

SELECT 
    CompanyName,
    SUM(t.Quantity * b.Cost) AS TotalCost,
    SUM(t.Quantity) AS Qty
FROM tblSFCOrder o
INNER JOIN tblSFCClient c
        ON o.ClientNumber = c.ClientNumber
INNER JOIN tblSFCOrderTxn t
        ON o.OrderID = t.OrderID
INNER JOIN tblSFCInventory i
        ON t.SKU = i.SKU
       AND o.ClientNumber = i.ClientNumber
INNER JOIN tblSFCBox b
        ON i.BoxID = b.Id
INNER JOIN tblSFCShipment s
        ON o.OrderID = s.OrderID
WHERE 
        o.StatusID = 4
    AND o.ClientNumber = 1486
    AND shipdate BETWEEN '2015-10-01 00:40:01.370' 
                     AND '2015-10-31 23:23:38.163'
GROUP BY 
        CompanyName

Upvotes: 0

shurik
shurik

Reputation: 805

Change TotalCost to

 SUM(t.Quantity * t.Cost) AS TotalCost

and remove b.Cost from group by, only group by CompanyName

 group by CompanyName

Upvotes: 2

Related Questions