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:
| 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
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
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;
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
- This one works.... didn't know that
in()could be used like this. Thank you!