CODEWITHSUNDEEP
mysqlsqlgroup-bycountwhere-clause
sebrenner
sebrenner

Reputation: 83

How to select rows with WHERE statement and COUNT(*) = 1

I am trying to combine a WHERE clause with a HAVING COUNT(*) clause. But I can't get the results I want.

I want to find all the customers that have placed only one order and the order is "inactive". Put another way, I am trying to select rows from an order table where the count of customer_name value is "1" and the order status equals "inactive".

I have this table:

orders_tbl
+----+--------------+-------------+---------+-------+
| ID |    cust_name |      status | item_no | price |
+----+--------------+-------------+---------+-------+
|  1 |        Scott |      active |       4 |   2.0 |
+----+--------------+-------------+---------+-------+
|  2 |        James |      active |       2 |   4.0 |
+----+--------------+-------------+---------+-------+
|  3 |         Eric |    inactive |       3 |   8.0 |
+----+--------------+-------------+---------+-------+
|  4 |        Polly |      active |       3 |   2.0 |
+----+--------------+-------------+---------+-------+
|  5 |        Peggy |    inactive |       6 |   4.0 |
+----+--------------+-------------+---------+-------+
|  6 |         Earl |    inactive |       1 |   5.0 |
+----+--------------+-------------+---------+-------+
|  7 |        Billy |      active |       4 |   2.0 |
+----+--------------+-------------+---------+-------+
|  8 |        Peggy |    inactive |       5 |   4.0 |
+----+--------------+-------------+---------+-------+
|  9 |        Jenny |    inactive |       4 |   8.0 |
+----+--------------+-------------+---------+-------+
| 10 |        Polly |      active |       2 |   2.0 |
+----+--------------+-------------+---------+-------+
| 11 |        Scott |    inactive |       2 |   4.0 |
+----+--------------+-------------+---------+-------+
| 12 |        James |    inactive |       1 |   8.0 |
+----+--------------+-------------+---------+-------+

I want the name of every client with only one order and the order is "inactive". From the table above, I want these results:

+----+--------------+-------------+---------+-------+
| ID | cust_name    |     status  | item_no | price |
+----+--------------+-------------+---------+-------+
|  3 |         Eric |    inactive |       3 |   8.0 |
+----+--------------+-------------+---------+-------+
|  6 |         Earl |    inactive |       1 |   5.0 |
+----+--------------+-------------+---------+-------+
|  9 |        Jenny |    inactive |       4 |   8.0 |
+----+--------------+-------------+---------+-------+

I tried this query, as well as many variations using WHERE and COUNT():

SELECT ID, cust_name, status, item_no, price, COUNT(*) 
FROM orders_tbl
WHERE status = 'inactive'
GROUP BY cust_name
HAVING COUNT(*)<2;

The above query produced results close to what I want. But I am getting customers that have one "inactive" record even if they have one or more active records. There are the results I get:

orders_tbl
+----+--------------+-------------+---------+-------+
| ID |    cust_name |      status | item_no | price |
+----+--------------+-------------+---------+-------+
|  3 |         Eric |    inactive |       3 |   8.0 |
+----+--------------+-------------+---------+-------+
|  5 |        Peggy |    inactive |       6 |   4.0 |
+----+--------------+-------------+---------+-------+
|  6 |         Earl |    inactive |       1 |   5.0 |
+----+--------------+-------------+---------+-------+
|  9 |        Jenny |    inactive |       4 |   8.0 |
+----+--------------+-------------+---------+-------+
| 11 |        Scott |    inactive |       2 |   4.0 |
+----+--------------+-------------+---------+-------+
| 12 |        James |    inactive |       1 |   8.0 |
+----+--------------+-------------+---------+-------+

Upvotes: 1

Views: 1073

Answers (1)

GMB
GMB

Reputation: 222492

One solution would be to use aggregation, and to to move the logic to a HAVING clause.The following query can give you the names of the customers that satisfy the conditions : :

SELECT cust_name
FROM mytable
GROUP BY cust_name
HAVING COUNT(*) = 1 AND MAX(status) = 'inactive'

Returns :

| cust_name |
| --------- |
| Earl      |
| Eric      |
| Jenny     |

If you want to see the orders as well then you can turn this into a subquery :

SELECT *
FROM mytable
WHERE cust_name IN (
    SELECT cust_name
    FROM mytable
    GROUP BY cust_name
    HAVING COUNT(*) = 1 AND MAX(status) = 'inactive'
)

Results :

| ID  | cust_name | status   | item_no | price |
| --- | --------- | -------- | ------- | ----- |
| 3   | Eric      | inactive | 3       | 8     |
| 6   | Earl      | inactive | 1       | 5     |
| 9   | Jenny     | inactive | 4       | 8     |

Demo on DB Fiddle

Starting MySQL 8.0, window functions make it much easier, and more efficient. You can inline them to count the total number of orders by customer, along with the count of inactive orders ; then just fiter in records where both values are 1 :

SELECT * FROM (
    SELECT 
        t.*, 
        COUNT(*) OVER(PARTITION BY cust_name) cnt_total, 
        SUM(status = 'inactive') OVER(PARTITION BY cust_name) cnt_inactive
    FROM mytable t
 ) x WHERE cnt_total = 1 AND cnt_inactive = 1;

Demo on DB Fiddle

Upvotes: 2

Related Questions