CODEWITHSUNDEEP
sqlsql-serveraggregate-functions
Bob
Bob

Reputation: 23010

How to use aggregate function?

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?

Upvotes: 0

Views: 46

Answers (3)

Uriil
Uriil

Reputation: 12628

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

Upvotes: 1

Jacob Lambert
Jacob Lambert

Reputation: 7679

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;

Upvotes: 1

diokey
diokey

Reputation: 176

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

Upvotes: 0

Related Questions