Running complex query in mysql

I have read multiple answers about this but still cannot manage to do it.

So i have table like this:

TABLE 1

| ID     |  COL1    |  COL2    |
| 1      |  10      |  Item_1  |
| 2      |  10      |  Item_2  |
| 3      |  10      |  Item_3  |
| 4      |  11      |  Item_2  |
| 4      |  11      |  Item_4  |
| 5      |  12      |  Item_1  |
| 6      |  12      |  Item_3  |

First i need to run this command SELECT COL1 FROM TABLE1 WHERE COL2 = ITEM2 GROUP BY COL1 This command returns me 10 and 11

Now after that i need to run this: SELECT COUNT(ID) AS 'COUNT', COL2 from TABLE1 group by COL2 order by 'COUNT' DESC LIMIT 5 and here i need to add WHERE statement so it doesn't run on whole table but only where COL1 = any result from first selection

So second command would return something like this:

2    Item_2
1    Item_1
1    Item_3
1    Item_4

So how can i achieve this from one query?

You can filter in the where:

SELECT COUNT(T1.ID) AS cnt, COL2 
FROM TABLE1 T1
WHERE T1.COL1 IN (SELECT TT1.COL1
                  FROM TABLE1 TT1
                  WHERE TT1.COL2 = 'ITEM2'
                 )
GROUP BY T1.COL2 
ORDER BY cnt DESC
LIMIT 5;

Note that IN removes duplicates, so GROUP BY (or SELECT DISTINCT) are not needed.

For that measure, we need to look at the table that will show running MySQL queries which is done by the processlist command: show full processlist; The FULL modifier allows us to see the query text in its entirety instead of the first 100 symbols we would get without this modifier.

try like below

    select col2,count(*) as cnt from table
    where condition 1st query
    group by col2
order by cnt 
limit 5

Teams. Q&A for Work. Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information.

SELECT 
    COUNT(id) as Count_name,
    col_2 
FROM 
    Count_54
WHERE 
    col_1 in (
        SELECT 
            DISTINCT col_1 
        FROM
            Count_54 
        WHERE
            col_2='Item_2'
    )
GROUP BY 
    col_2 
ORDER BY 
    col_1 DESC;

MySQL has a statement called "show processlist" to show you the running queries on your MySQL server. This can be useful to find out what’s going on if there are some big, long queries consuming a lot of CPU cycles, or if you’re getting errors like "too many connections". The syntax is simply:

Using The slow query log. Another valuable tool in MySQL is the included slow query logging feature. This feature is the preferred method for finding long running queries on a regular basis. There are several directives available to adjust this feature. However, the most commonly needed settings are:

You can set the MySQL query cache values by editing the configuration file ('/etc/mysql/my.cnf' or '/etc/mysql/mysql.conf.d/mysqld.cnf'). This will depend on your MySQL installation. Don't set a

Before you can profile slow queries, you need to find them. MySQL has a built-in slow query log. To use it, open the my.cnf file and set the slow_query_log variable to "On." Set long_query_time to the number of seconds that a query should take to be considered slow, say 0.2. Set slow_query_log_file to the path where you want to save the file

Comments
  • This one works.... didn't know that in() could be used like this. Thank you!