How do I get Sum of two Columns with Same Foreign Key

Question

I have three tables: Carrier(C), DropShipper(D) and ShoppingCart(S) with the following schema

Table C

c.id(Pk,int,not null)
c.dropshipperid(Fk,int,not null)
c.Prodid(int, not null
c.cost(money null)

Table D

D.Dropshipperid(Pk,int,not null)
D.Dropshipper(nvarchar(50))
D.Remarks(nvarchar(50))

Table S

s.cartid(PK, char(36))
s.prodid(pk,fk,int not null)
s.qty(int not null)

Here are sample data:

c.id    c.dropshipperid  c.prodid    c.cost
--------------------------------------------
1            1             11          100
2            2             11          200
3           3             11          80
4            4             11           70
5            1             6          212
6            2             6          312
7           3             6          412
8           4             6          512


D.dropshipperid   D.dropshipper        D.Remarks
-------------------------------------------------
1                   Airmail               10-25days
2                    DHL                  23-5 days
3                     Fedex                6- 10days
4                       UPS                 4- 5days

S.cartid           s.prodid              s.qty
------------------------------------------------
   xxxx                 11                  2
    xxxx                 6                   2

And here is my sql

SELECT     D.DropShipper, C.Cost, D.Remarks,( S.Quantity * C.Cost)    AS  SubCost, S.CartID, C.DropShipperID, 
S.ProductID, C.ProductID AS cProductid,  S.Quantity
FROM C INNER JOIN D 
ON C.DropShipperID = D.DropShipperID 
INNER JOIN S 
 ON S.ProductID = S.ProductID
 WHERE (C.DropShipperID IN (1, 2, 3, 4, 5)) AND
(S.CartID = @cartid)

This is a sample result of my qry:

Dropshipper     cost     Remarkd     Subcost     Cartid    Dropshipperid
------------------------------------------------------------------------
   Airmail       100      Text         200        xxxx            1
   DHL           200                   400         xxxx           2   
   Fedex          80                   160         xxxx            3
   UPS            70                   140        xxxx            4
  Airmail         212                 424            xxxx          1
   DHL           312                  624          xxxx            2
   Fedex         412                  824         xxxx            3
   UPS           512                  1024        xxxx             4

Here is what I need:

I don Not what the DropShipperID duplicated, ie I need just one set. Then SubCost should be the sum of subcost for each dropshipperID. some thing like this

DropShipperID   DropShipper    SubCost    etc
------------------------------------------------
1                Airmail          624
2                DHL               1014
3                Fedex              974
4                UPS                 1164  

Answer 1

Try this,

 SELECT C.DropShipperID, D.DropShipper, 
        SUM(S.Quantity) AS Quantity, 
        SUM(C.Cost) AS Cost, 
        SUM(S.Quantity * C.Cost) AS SubCost
 FROM C INNER JOIN D ON C.DropShipperID = D.DropShipperID 
        INNER JOIN S ON S.ProductID = S.ProductID
 WHERE (C.DropShipperID IN (1, 2, 3, 4, 5))
       AND (S.CartID = @cartid)
 GROUP BY C.DropShipperID, D.DropShipper

All the columns in the select list should appear in the goup by clause, except the columns used with aggregate functions like sum(), max(), min() etc.

In your case columns like Quantity, Cost etc should be used with in SUM() function in the select list, and should not be used in the group by clause.