Syntax error Set an alternative of WHERE clause for each SELECT field in MySQL

Set an alternative of WHERE clause for each SELECT field in MySQL

You can use CASE statement −

mysql> create table DemoTable1988
   (
   Value1 int,
   Value2 int,
   Price int
   );
Query OK, 0 rows affected (0.64 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1988 values(10,7,500);
Query OK, 1 row affected (0.21 sec)
mysql> insert into DemoTable1988 values(7,9,400);
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable1988 values(8,7,200);
Query OK, 1 row affected (0.17 sec)
mysql> insert into DemoTable1988 values(7,4,300);
Query OK, 1 row affected (0.16 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1988;

This will produce the following output −

+--------+--------+-------+
| Value1 | Value2 | Price |
+--------+--------+-------+
|     10 |      7 |   500 |
|      7 |      9 |   400 |
|      8 |      7 |   200 |
|      7 |      4 |   300 |
+--------+--------+-------+
4 rows in set (0.00 sec)

Here is the query to WHERE clause for each SELECT field:

mysql> select sum( case when Value2=7 then Price else 0 end)-
   sum( case when Value1=7 then Price else 0 end) as Result
   from DemoTable1988;

This will produce the following output −

+--------+
| Result |
+--------+
|      0 |
+--------+
1 row in set (0.23 sec)
Updated on: 2020-01-02T05:15:50+05:30

509 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements