Question 290066 in Mysql Question posted in
The official MySQL documentation can be found here.

MySQL Select rows with multiple column entries

I’m not sure how to word the question for the title. If there is a correct way to word this question better, I would appreciate it.

I have a denormalized table, records, with columns:

biz_address, customer_name, dept_ID, order_ID, order_amount.

biz_address and customer_name have duplicate column entries.

How can I get list of all orders for addresses with more than 5 orders per address?

What I want is a result set like so…

+-----------------+-----------+------------+----------+-----------+
| biz_address | customer_name | dept_ID  | order_ID | order_amount |
+-----------------+-----------+------------+----------+-----------+
| 100   CLAY DR   | REAL      | 1     | 54    |     60 |
| 100   CLAY DR   | REAL      | 2     | 55    |      4 |
| 100   CLAY DR   | REAL      | 3     | 56    |      5 |
...
| 11    MOORE ST  | ABCO      | 10    | 67       |     7  |
| 11    MOORE ST  | ABCO      | 11    | 78       |     2  |
| 11    MOORE ST  | ABCO      | 12    | 79       |     4  |
| 11    MOORE ST  | ABCO      | 13    | 80       |     8  |

I know how to get a count:

SELECT biz_address, customer_name, order_ID, COUNT(*) 
FROM records
GROUP BY customer_name
HAVING COUNT(*) > 5;

Which returns this…

+----------------+------------+------------+----------+
| biz_address   | customer_name | order_ID   | COUNT(*) |
+--------+------------+---------------+----------+
| 100   CLAY DR    | REAL    | 54      |        6 |
| 11    MOORE ST   | ABC     | 67      |        8 |

Thanks

Tags:

2

Answers


  1. 0

    Join your original table with a subquery that gets all the addresses with the specified number of duplicates.

    WITH dups AS (
    SELECT biz_address
    FROM records
    GROUP BY biz_address
    HAVING COUNT(*) > 5
)
SELECT r.*
FROM records AS r
JOIN dups ON r.biz_address = dups.biz_address
    Login or Signup to reply.
  2. 0

    Here’s an alternative solution using COUNT() as a window function instead of an aggregation function.

    SELECT *
FROM (
  SELECT *, COUNT(*) OVER (PARTITION BY biz_address) AS count
  FROM records
) AS t
WHERE count >= 4;

    Window functions require MySQL 8.0 or later.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search