SQL Server: Comparing two columns in a where clause

I'm very new to using SQL, so the "for idiots" version is preferred.

I'm trying to write a query that only displays rows where one column's value is equal to another's. I know people here have already asked this same question, but the solutions posted there didn't work for me. Here is the query:

USE [AdventureWorks2017]

SELECT 
    [SalesOrderID],
    [LineTotal] AS "Listed total price",
    [ActualLineTotal] AS "actual total price"
WHERE 
    [LineTotal] <> [ActualUnitPrice]
FROM 
    [Sales].[SalesOrderDetail]

In each and every attempt I've made, I've gotten the error

Incorrect syntax near the keyword 'FROM'

The query works when I remove the WHERE line.

Do the data types matter? LineTotal is Computed,numeric(38,6), not null and ActualLineTotal is money, not null

2 answers

  • answered 2020-10-16 04:13 Sekhar

    Can you try swapping the lines (WHERE should come after FROM)

    USE [AdventureWorks2017]
    
    
    SELECT [SalesOrderID]
    , [LineTotal] AS "Listed total price"
    , [ActualLineTotal] AS "actual total price"
    FROM [Sales].[SalesOrderDetail]
    WHERE [LineTotal] <> [ActualUnitPrice]
    

  • answered 2020-10-16 04:24 Venkataraman R

    SQL is english like language. So, the syntax will be something like below:

    SELECT [SalesOrderID]
    , [LineTotal] AS "Listed total price"
    , [ActualLineTotal] AS "actual total price"
    FROM [Sales].[SalesOrderDetail]
    WHERE [LineTotal] <> [ActualUnitPrice]
    

    A Simple query for easier understanding. We SELECT columns by INNER JOINing two tables, applying WHERE clause and then applying GROUP BY and groups HAVING certain criteria matching and finally ORDERing the results.

    SELECT C.custid, COUNT( O.orderid ) AS numorders
    FROM Sales.Customers AS C
    INNER JOIN Sales.Orders AS O
    ON C.custid = O.custid
    WHERE C.country = N'Spain'
    GROUP BY C.custid
    HAVING COUNT( O.orderid ) <= 3
    ORDER BY numorders;