Syntax error MySQL query to select rows where column value is only 0, group by another column?

MySQL query to select rows where column value is only 0, group by another column?

For this, use group by. Let us first create a table −

mysql> create table DemoTable1344
   -> (
   -> `SequenceId` int NOT NULL AUTO_INCREMENT PRIMARY KEY,
   -> ClientId int,
   -> isMarried tinyint(1)
   -> );
Query OK, 0 rows affected (0.49 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1344(ClientId,isMarried) values(4567,0);
Query OK, 1 row affected (0.45 sec)
mysql> insert into DemoTable1344(ClientId,isMarried) values(9876,0);
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable1344(ClientId,isMarried) values(5432,1);
Query OK, 1 row affected (0.12 sec)
mysql> insert into DemoTable1344(ClientId,isMarried) values(4567,1);
Query OK, 1 row affected (0.18 sec)
mysql> insert into DemoTable1344(ClientId,isMarried) values(9873,0);
Query OK, 1 row affected (0.15 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1344;

This will produce the following output −

+------------+----------+-----------+
| SequenceId | ClientId | isMarried |
+------------+----------+-----------+
|          1 |     4567 |         0 |
|          2 |     9876 |         0 |
|          3 |     5432 |         1 |
|          4 |     4567 |         1 |
|          5 |     9873 |         0 |
+------------+----------+-----------+
5 rows in set (0.00 sec)

Following is the query to select rows where column value is only 0, group by another column −

mysql> select SequenceId,ClientId,isMarried
-> from DemoTable1344
-> group by ClientId
-> having isMarried=0;

This will produce the following output −

+------------+----------+-----------+
| SequenceId | ClientId | isMarried |
+------------+----------+-----------+
|          1 |     4567 |         0 |
|          2 |     9876 |         0 |
|          5 |     9873 |         0 |
+------------+----------+-----------+
3 rows in set (0.00 sec)
Updated on: 2019-11-05T07:47:33+05:30

288 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements