Geek Answers Handbook

How can I make two conditions in a sentence

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 request the number of Domains that have 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';

This 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

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

+7
source share
3 answers

As described in the SELECT Syntax:

The HAVING is applied almost the last, just before sending items to the client, without optimization.

In other words, it is applied after performing a grouping operation (unlike WHERE , which is performed before any grouping operation). See WHERE vs HAVING .

Therefore, your current query first generates a result set from the following:

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

Take a look at 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 just one of the values ​​from each group, as described in MySQL Extensions for GROUP BY :

The server can select any value from each group, therefore, if they do not match, the selected values ​​are undefined.

Then your current query filters these results according to your HAVING :

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

With the file values ​​selected above, each individual group meets the second criterion; and the first two groups correspond to the first criterion. Therefore, the results of a full query :

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

Following your comments above , you want to filter the entries on file before grouping and then filter the resulting groups for those that contain more than one match. So 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

Take a look at sqlfiddle :

  |  NUMBER |  SUM DOMAINS |
 ------------------------
 |  222 |  2 |
+5
source

Try the nested query below:

  select count(domain) as 'sum domains', domain as fileName from (select domain, file from tableName group by Number having count(Number) >1) as temp WHERE file like '%\_1';
0
source

You cannot have a file name in a SELECT if it is also not in GROUP BY . You should get a GROUP BY result than a JOIN back to the original and add filter logic as follows:

 SELECT * FROM ( select count(domain) as 'sum_domains', Number from table group by Number having count(Number) >1 ) result join table t on result.Number = t.Number WHERE file like '%\_1'
0
source

More articles:


All Articles