How can I make two condition in having clause

Question

I have a table similar to:

domain      |   file      | Number 
------------------------------------     
aaa.com     | aaa.com_1   | 111
bbb.com     | bbb.com_1   | 222
ccc.com     | ccc.com_2   | 111
ddd.com     | ddd.com_1   | 222
eee.com     | eee.com_1   | 333

I need to query the number of Domains that share the same Number and their File name ends with _1. I tried the following:

select count(domain) as 'sum domains', file
from table 
group by Number
having
count(Number) >1 and File like '%\_1'; 

It gives me:

sum domains | file
------------------------------
2           | aaa.com
2           | bbb.com

I expected to see the following:

sum domains | file
------------------------------
1           | aaa.com
2           | bbb.com

Because the Number 111 appears once with File ends with _1 and _2, so it should count 1 only. How can I apply the 2 conditions that I stated earlier correctly ?

Answer 1

i am using by following

having ( SUM(qty) > 4 AND SUM(qty) < 15 )

Answer 2

As documented under SELECT Syntax:

The HAVING clause is applied nearly last, just before items are sent to the client, with no optimization.

In other words, it is applied after the grouping operation has been performed (in contrast with WHERE, which is performed before any grouping operation). See WHERE vs HAVING.

Therefore, your current query first forms the resultset from the following:

SELECT   COUNT(domain) AS `sum domains`, file
FROM     `table`
GROUP BY Number

See it on sqlfiddle:

| SUM DOMAINS |      FILE |
---------------------------
|           2 | aaa.com_1 |
|           2 | bbb.com_1 |
|           1 | eee.com_1 |

As you can see, the values selected for the file column are merely one of the values from each group—as documented under MySQL Extensions to GROUP BY:

The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate.

Your current query then proceeds to filter these results according to your HAVING clause:

HAVING   COUNT(Number) > 1 AND file LIKE '%\_1'

With the values of file selected above, every single group matches on the second criterion; and the first two groups match on the first criterion. Therefore the results of the complete query are:

| SUM DOMAINS |      FILE |
---------------------------
|           2 | aaa.com_1 |
|           2 | bbb.com_1 |

Following your comments above, you want to filter the records on file before grouping and then filter the resulting groups for those containing more than one match. Therefore use WHERE and HAVING respectively (and select Number instead of file to identify each group):

SELECT   Number, COUNT(*) AS `sum domains`
FROM     `table`
WHERE    file LIKE '%\_1'
GROUP BY Number
HAVING   `sum domains` > 1

See it on sqlfiddle:

| NUMBER | SUM DOMAINS |
------------------------
|    222 |           2 |