FORMAT(SUM(Value*Value), 'C') does not allow me to order by DESC properly-CodePudding

I'm currently trying to list OrderRevenue descending, but it lists the values descending improperly when I test my query.

What I've tried using:

SELECT o.OrderID, DATEPART(YEAR,o.OrderDate) AS OrderYear, DATEPART(QUARTER,o.OrderDate) AS OrderQuarter,
       E.FirstName   ' '   E.LastName AS EmployeeName, C.CustomerName, S.ShipperName, SUM(d.Quantity) AS OrderItems, FORMAT(SUM(d.Quantity*p.Price), 'C') AS OrderRevenue
  FROM Orders o
    INNER JOIN Employees e
      ON o.EmployeeID = e.EmployeeID
    INNER JOIN Customers c
      ON o.CustomerID = c.CustomerID
    INNER JOIN Shippers s
      ON o.ShipperID = s.ShipperID
    INNER JOIN OrderDetails d
      ON o.OrderID = d.OrderID
    INNER JOIN Products p
      ON  d.ProductID = p.ProductID
 GROUP BY o.OrderID, DATEPART(YEAR,o.OrderDate), DATEPART(QUARTER,o.OrderDate),
       E.FirstName   ' '   E.LastName, C.CustomerName, S.ShipperName, P.Price
ORDER BY OrderRevenue DESC

What it prints out for me:

As you can see it is not properly descending in value. Did I use the Format() method incorrectly?

The SQL Schema I'm using is public, so feel free to test your query results on W3 if that would help you, help me better: https://www.w3schools.com/sql/trysqlserver.asp?filename=trysql_func_sqlserver_datepart

Would really appreciate any explanation as to why SQL Server doesn't like my code. Thanks!

CodePudding user response：

You can add this ORDER BY SUM(d.Quantity*p.Price) DESC in order by

SELECT o.OrderID, DATEPART(YEAR,o.OrderDate) AS OrderYear, DATEPART(QUARTER,o.OrderDate) AS OrderQuarter,
       E.FirstName   ' '   E.LastName AS EmployeeName, C.CustomerName, S.ShipperName, SUM(d.Quantity) AS OrderItems, FORMAT(SUM(d.Quantity*p.Price), 'C') AS OrderRevenue
  FROM Orders o
    INNER JOIN Employees e
      ON o.EmployeeID = e.EmployeeID
    INNER JOIN Customers c
      ON o.CustomerID = c.CustomerID
    INNER JOIN Shippers s
      ON o.ShipperID = s.ShipperID
    INNER JOIN OrderDetails d
      ON o.OrderID = d.OrderID
    INNER JOIN Products p
      ON  d.ProductID = p.ProductID
 GROUP BY o.OrderID, DATEPART(YEAR,o.OrderDate), DATEPART(QUARTER,o.OrderDate),
       E.FirstName   ' '   E.LastName, C.CustomerName, S.ShipperName, P.Price
ORDER BY SUM(d.Quantity*p.Price) DESC

Or you can do it like this also

select *,FORMAT(f1.OrderRevenue, 'C') as OrderRevenue from (SELECT o.OrderID, DATEPART(YEAR,o.OrderDate) AS OrderYear, DATEPART(QUARTER,o.OrderDate) AS OrderQuarter,
       E.FirstName   ' '   E.LastName AS EmployeeName, C.CustomerName, S.ShipperName, SUM(d.Quantity) AS OrderItems, SUM(d.Quantity*p.Price) AS OrderRevenue
  FROM Orders o
    INNER JOIN Employees e
      ON o.EmployeeID = e.EmployeeID
    INNER JOIN Customers c
      ON o.CustomerID = c.CustomerID
    INNER JOIN Shippers s
      ON o.ShipperID = s.ShipperID
    INNER JOIN OrderDetails d
      ON o.OrderID = d.OrderID
    INNER JOIN Products p
      ON  d.ProductID = p.ProductID
 GROUP BY o.OrderID, DATEPART(YEAR,o.OrderDate), DATEPART(QUARTER,o.OrderDate),
       E.FirstName   ' '   E.LastName, C.CustomerName, S.ShipperName, P.Price) f1
ORDER BY f1.OrderRevenue DESC