user2455595 user2455595 - 5 months ago 6
SQL Question

How to get information without group on the column

I need to get top according to

SUM(d.OrderQty)
, but I need also information according to specific filter (
OrderSourceID
).

When I add it to the group it change the order logic and return multiple lines for same number. What is the correct way to do this SQL?

SELECT *
FROM
(SELECT TOP (@NumOfRecords)
i.Number as Number,
i.Description as Description,
SUM(d.OrderQty) as TotalCount,
CASE
WHEN h.OrderSourceID = 1 THEN SUM(d.OrderQty)
ELSE 0
END as TotalSource_1,
CASE
WHEN h.OrderSourceID = 2 THEN SUM(d.OrderQty)
ELSE 0
END as TotalSource_2,
CASE
WHEN h.OrderSourceID = 3 THEN SUM(d.OrderQty)
ELSE 0
END as TotalSource_3
FROM
tblHeader h
INNER JOIN
tblDetail d on h.SalesOrderID = d.SalesOrderID
INNER JOIN
tblItemDetail id on id.ItemID = d.ItemID
INNER JOIN
tblItem i on d.ItemID = i.ItemID
WHERE
CONVERT(DATE, h.OrderDate) BETWEEN @StartDate AND @EndDate
GROUP BY
id.ItemID, i.Number, i.Description, h.OrderSourceID
ORDER BY
SUM(d.OrderQty) DESC) x
ORDER BY
x.Number

Answer

If I've understood the question correctly then you just need to move the references to h.OrderSourceID inside the SUM, like this;

SELECT * 
FROM
    (SELECT TOP (@NumOfRecords) 
         i.Number as Number,
         i.Description as Description,
         SUM(d.OrderQty) as TotalCount,
         SUM(CASE WHEN h.OrderSourceID = 1 THEN d.OrderQty ELSE 0 END) as TotalSource_1,
         SUM(CASE WHEN h.OrderSourceID = 2 THEN d.OrderQty ELSE 0 END) as TotalSource_2,
         SUM(CASE WHEN h.OrderSourceID = 3 THEN d.OrderQty ELSE 0 END) as TotalSource_3
     FROM 
         tblHeader h
     INNER JOIN 
         tblDetail d on h.SalesOrderID = d.SalesOrderID
     INNER JOIN 
         tblItemDetail id on id.ItemID = d.ItemID
     INNER JOIN 
         tblItem i on d.ItemID = i.ItemID
     WHERE
         CONVERT(DATE, h.OrderDate) BETWEEN @StartDate AND @EndDate
     GROUP BY
         id.ItemID, i.Number, i.Description
     ORDER BY
         SUM(d.OrderQty) DESC) x
ORDER BY
    x.Number