Need help on an SQL Select query

I have a table, table1 – with columns name,status,number,type,col2,col3

I need to display the name and number field from this table when the count of a particular status (W) in the whole table is greater than 5

I can display if the count of status ‘W’ is greater than 5 using

SELECT COUNT(*) AS COUNT FROM table1  WHERE status='W' HAVING COUNT(*) > 24;  

If I use the unique column of number and try to write the query the count(*) will always be 1 as the number is unique

SELECT a.name ,a.number,count(*) as count from table1 A where status='E' group by a.name ,a.number 

data from table1

Any help is appreciated thanks 🙂

Asked on September 1, 2020 in Mysql.
Add Comment
1 Answer(s)

I need to display the name and number field from this table when the count of a particular status (W) in the whole table is greater than 5

You are describing:

select t.* from t where (select count(*) from t where status = 'W') > 5; 

This doesn’t seem to me like it does anything useful, but that is what you are asking for.

Answered on September 1, 2020.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.