CODEWITHSUNDEEP
mysql
SammyBlackBaron
SammyBlackBaron

Reputation: 897

MySQL group by with where clause with having count greater than 1

This should be really simple I know, but for the life of me I can't get my query to do what I need.

I am looking to find all rows that are a certain status (paid) grouped by ref but only if there is more than 1 row found.

This is my sample table:

+-----+----------+----------+-------+
| id  | deleted  | status   |  ref  |
+-----+----------+----------+-------+
|   1 |        0 | pending  | 10001 |
|   2 |        0 | paid     | 10001 |
|   3 |        0 | paid     | 10001 |
|   4 |        0 | paid     | 10002 |
|   5 |        1 | pending  | 10002 |
|   6 |        1 | paid     | 10002 |
|   7 |        0 | pending  | 10003 |
|   8 |        0 | paid     | 10003 |
|   9 |        0 | paid     | 10003 |
|  10 |        0 | paid     | 10003 |
|  11 |        0 | pending  | 10004 |
|  12 |        0 | paid     | 10004 |
|  13 |        1 | pending  | 10005 |
|  14 |        1 | paid     | 10005 |
|  15 |        1 | paid     | 10005 |
|  16 |        0 | paid     | 10005 |
|  17 |        0 | pending  | 10006 |
|  18 |        0 | paid     | 10006 |
|  19 |        0 | paid     | 10006 |
+-----+----------+----------+-------+

This is my SQL:

SELECT * FROM `orders`
WHERE `deleted` = 0 AND `status` = 'paid'
GROUP BY SUBSTR(`ref`,0,5)
HAVING COUNT(*) > 1
ORDER BY `id` DESC

I need to have it matching by SUBSTR due to ref sometimes containing appended numbers.

The problem is that my query is returning this:

+-----+----------+---------+-------+
| id  | deleted  | status  |  ref  |
+-----+----------+---------+-------+
|   2 |        0 | paid    | 10001 |
+-----+----------+---------+-------+

When I'd like it to be returning refs 10001, 10003 & 10006.

Can anyone help me work out what I am doing wrong?

Thanks

Upvotes: 15

Views: 63809

Answers (4)

thennarasu
thennarasu

Reputation: 1

We can reduce this query as :

SELECT * from order
WHERE status="paid"
GROUP BY ref
HAVING COUNT(*) > 1;

Upvotes: 0

vishy dewangan
vishy dewangan

Reputation: 1069

The query should be

SELECT * from order
WHERE status="paid"
GROUP BY SUBSTRING('ref',1,5)
HAVING COUNT(*) > 1;

Upvotes: 0

barsju
barsju

Reputation: 4446

From SUBSTR doc:

For all forms of SUBSTRING(), the position of the first character in the string from which the substring is to be extracted is reckoned as 1.

So try this:

SELECT * FROM `orders`
WHERE `deleted` = 0 AND `status` = 'paid'
GROUP BY SUBSTR(`ref`,1,5)
HAVING COUNT(*) > 1
ORDER BY `id` DESC

Upvotes: 8

Maximilian Mayerl
Maximilian Mayerl

Reputation: 11357

Try

SELECT * FROM `orders`
WHERE `deleted` = 0 AND `status` = 'paid'
GROUP BY SUBSTR(`ref`,1,5)
HAVING COUNT(*) > 1
ORDER BY `id` DESC

The position-argument for SUBSTR starts with 1, not with 0.

Upvotes: 31

Related Questions