SQL query comparing values from two columns, evaluating it and making the sum of evaluated values-CodePudding

There are four columns in the table:

COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT

I need to write a SQL query that will compare values in columns PRICE_LEFT and PRICE_RIGHT, and assign a value of one to column with greater value (otherwise assigns 0).

Next I need to make a sum of all ones and zeros corresponding to each value in columns COL_LEFT and COL_RIGHT.

So far I can only make the comparison but I don't know how to make sums out of all the values in both columns (RESULT_LEFT, RESULT_RIGHT) in RESULT_TABLE.

Here's what I got so far:

 WITH Tbl AS
 (
     SELECT
         a.*,
         CASE 
             WHEN PRICE_LEFT > PRICE_RIGHT 
                 THEN 1 
                 ELSE 0 
         END AS RESULT_LEFT,
         CASE 
             WHEN PRICE_RIGHT > PRICE_LEFT 
                 THEN 1 
                 ELSE 0 
         END AS RESULT_RIGHT
     FROM 
         SUMS_BY_VALUE a
 )
 SELECT *
 FROM Tbl
      ....

I am using SQL Server LocalDB database.

Here is the example of how it should work:

Here's the DDL:

CREATE TABLE SUMS_BY_VALUE 
(
    COL_LEFT varchar(255),
    COL_RIGHT varchar(255),
    PRICE_LEFT int,
    PRICE_RIGHT int
);


INSERT INTO SUMS_BY_VALUE (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('B', 'G', 22, 4);
INSERT INTO SUMS_BY_VALUE (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('C', 'A', 15, 14);
INSERT INTO SUMS_BY_VALUE (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('B', 'D', 5, 18);
INSERT INTO SUMS_BY_VALUE (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('A', 'F', 2, 2);
INSERT INTO SUMS_BY_VALUE (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('F', 'E', 4, 8);
INSERT INTO SUMS_BY_VALUE (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('G', 'C', 16, 6);
INSERT INTO SUMS_BY_VALUE (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('D', 'C', 22, 28);
INSERT INTO SUMS_BY_VALUE (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('A', 'G', 14, 19);
INSERT INTO SUMS_BY_VALUE (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('F', 'D', 3, 12);
INSERT INTO SUMS_BY_VALUE (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('B', 'A', 11, 9);
INSERT INTO SUMS_BY_VALUE (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('D', 'F', 8, 2);
INSERT INTO SUMS_BY_VALUE (COL_LEFT, COL_RIGHT, PRICE_LEFT, PRICE_RIGHT) VALUES ('B', 'F', 4, 1);

CodePudding user response：

Try the following:

With CTE As
(
    Select COL_LEFT As Col, 
    Case When PRICE_LEFT> PRICE_RIGHT Then 1 Else 0 End as Result
    from SUMS_BY_VALUE
    Union ALL
    Select COL_RIGHT As Col,
    Case When PRICE_LEFT<PRICE_RIGHT Then 1 Else 0 End as Result
    from SUMS_BY_VALUE
)

Select Col,Sum(Result) As Tot From CTE
Group By Col

See a demo from here.