How to use aggregate function?

Question

I have the following query:

SELECT dbo.saleDocumentDetails.ID,
       dbo.saleDocumentDetails.DocumentID,
       dbo.saleDocumentDetails.invProductID,
       dbo.saleDocumentDetails.Qty,
       dbo.saleDocumentDetails.QtyConfirmed,
       dbo.saleDocumentDetails.AmountPrice,
       dbo.saleDocumentDetails.AmountUserPrice,
       dbo.saleDocumentDetails.AmountCost,
       dbo.saleDocumentDetails.RespiteDays,
       dbo.saleDocumentDetails.Date_UpdateLast,
       CASE 
            WHEN io1.Code = '33' THEN SUM(idd.Qty)
            ELSE SUM(0)
       END                               AS Qty_BackSale,
       CASE 
            WHEN io1.Code = '41' THEN SUM(idd.Qty)
            ELSE SUM(0)
       END                               AS Qty_Stock
FROM   dbo.saleDocuments
       INNER JOIN dbo.saleDocumentDetails
            ON  dbo.saleDocuments.ID = dbo.saleDocumentDetails.DocumentID
       LEFT OUTER JOIN dbo.invDocumentDetails   AS  idd
            ON  dbo.saleDocumentDetails.ID = idd.saleDocumentDetailID
       LEFT OUTER JOIN dbo.invDocuments  AS id2
            ON  id2.ID = idd.invDocumentID
       LEFT JOIN dbo.invOperations       AS io1
            ON  (io1.ID = id2.invOperationID)
WHERE  (dbo.saleDocumentDetails.ID = 1295617)
GROUP BY
       dbo.saleDocumentDetails.ID,
       dbo.saleDocumentDetails.DocumentID,
       dbo.saleDocumentDetails.invProductID,
       dbo.saleDocumentDetails.Qty,
       dbo.saleDocumentDetails.QtyConfirmed,
       dbo.saleDocumentDetails.AmountPrice,
       dbo.saleDocumentDetails.AmountUserPrice,
       dbo.saleDocumentDetails.AmountCost,
       dbo.saleDocumentDetails.RespiteDays,
       dbo.saleDocumentDetails.Date_UpdateLast,
       io1.Code

its result is:

enter image description here

I would like to achieve:

enter image description here

How should I change the query?

Uriil · Accepted Answer · 2014-11-01 06:56:54Z

1

As mentioned JRLambert, move SUM() outside CASE, and remove io1.Code from GROUP BY

answered Nov 1, 2014 at 6:56

Uriil

12.8k11 gold badges54 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jacob Lambert · Accepted Answer · 2014-11-01 06:54:57Z

Move the SUM() in your CASE statements outside of the CASE:

   SELECT 
       dbo.saleDocumentDetails.ID,
       dbo.saleDocumentDetails.DocumentID,
       dbo.saleDocumentDetails.invProductID,
       dbo.saleDocumentDetails.Qty,
       dbo.saleDocumentDetails.QtyConfirmed,
       dbo.saleDocumentDetails.AmountPrice,
       dbo.saleDocumentDetails.AmountUserPrice,
       dbo.saleDocumentDetails.AmountCost,
       dbo.saleDocumentDetails.RespiteDays,
       dbo.saleDocumentDetails.Date_UpdateLast,
       SUM(CASE 
            WHEN io1.Code = '33' THEN idd.Qty
            ELSE 0
       END)                              AS Qty_BackSale,
       SUM(CASE 
            WHEN io1.Code = '41' THEN idd.Qty
            ELSE 0
       END)                              AS Qty_Stock
   FROM
       dbo.saleDocuments
       INNER JOIN dbo.saleDocumentDetails
           ON  dbo.saleDocuments.ID = dbo.saleDocumentDetails.DocumentID
       LEFT OUTER JOIN dbo.invDocumentDetails   AS  idd
           ON  dbo.saleDocumentDetails.ID = idd.saleDocumentDetailID
       LEFT OUTER JOIN dbo.invDocuments  AS id2
           ON  id2.ID = idd.invDocumentID
       LEFT JOIN dbo.invOperations       AS io1
           ON  (io1.ID = id2.invOperationID)
WHERE
    (dbo.saleDocumentDetails.ID = 1295617)
GROUP BY
    dbo.saleDocumentDetails.ID,
    dbo.saleDocumentDetails.DocumentID,
    dbo.saleDocumentDetails.invProductID,
    dbo.saleDocumentDetails.Qty,
    dbo.saleDocumentDetails.QtyConfirmed,
    dbo.saleDocumentDetails.AmountPrice,
    dbo.saleDocumentDetails.AmountUserPrice,
    dbo.saleDocumentDetails.AmountCost,
    dbo.saleDocumentDetails.RespiteDays,
    dbo.saleDocumentDetails.Date_UpdateLast,
    io1.Code;

diokey · Accepted Answer · 2014-11-01 06:49:06Z

0

Have you tried to remove group by dbo.saleDocumentDetails.ID ?

answered Nov 1, 2014 at 6:49

diokey

1761 gold badge2 silver badges12 bronze badges

2 Comments

Jacob Lambert Over a year ago

He needs to keep that.

Bob Over a year ago

Yes. but all of the columns are same except the two last columns.

Collectives™ on Stack Overflow

How to use aggregate function?

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related