CODEWITHSUNDEEP
mysqlsqlselectcount
Dibish
Dibish

Reputation: 9293

Sql select query with count

I have a fb_requests table. enter image description here

I would like to select game_selected column based on accept_status, if accept_status count < 4 , i want to select those rows. tried hard to get it working, please help me to solve this issue.

This is my create table code 

CREATE TABLE `fb_requests` (                                              
               `id` int(60) NOT NULL AUTO_INCREMENT,                                   
               `user_id` int(60) DEFAULT NULL,                                         
               `fb_user_id` varchar(255) DEFAULT NULL,                                 
               `request_id` varchar(255) DEFAULT NULL,                                 
               `game_selected` int(60) DEFAULT NULL,                                   
               `accept_status` int(60) DEFAULT NULL COMMENT '0 = pending 1 = accept',  
               `created_date` datetime DEFAULT NULL,                                   
               `modified_date` datetime DEFAULT NULL,                                  
               PRIMARY KEY (`id`)                                                      
             ) ENGINE=MyISAM AUTO_INCREMENT=190 DEFAULT CHARSET=latin1

Tried this code. i know its not syntactically correct but tried it. 

Select 
    game_selected 
from 
    fb_requests
where 
    user_id = 17 
    && 
    (
        count(accept_status =1) < 4
    ) 
group by 
    game_selected;

Thanks in advance.

Upvotes: 0

Views: 476

Answers (6)

ursitesion
ursitesion

Reputation: 988

Try below:

Select fbr1.game_selected,fbr1.* from fb_requests fbr1 JOIN 
(select id,accept_status from fb_requests where accept_status =1 limit 4) fbr2
 ON fbr1.id=fbr2.id where fbr1.user_id = 17;

Let me know if you are not looking for the result fetched from above query.

Upvotes: 1

Sid M
Sid M

Reputation: 4354

Try this

    Select game_selected from fb_requests
    where user_id=17 
    group by game_selected
    having count(accept_status)<4;

Update:

    Select game_selected from fb_requests
    where user_id=17 and accept_status=1
    group by game_selected
    having count(accept_status)<4;

Upvotes: 1

Aman Aggarwal
Aman Aggarwal

Reputation: 18449

Select game_selected from fb_requests where user_id=17 group by game_selected having count(accept_status)<4;

Upvotes: 0

Yograj Sudewad
Yograj Sudewad

Reputation: 343

Please try following queries as per your requirements 

Select game_selected,count(accept_status) as as_cnt
    from 
        fb_requests
    where 
        user_id = 17 
    group by 
        game_selected
 having as_cnt < 4;

OR

following query for only accept_status marked as '1'

    Select 
        game_selected,sum(accept_status) as as_cnt
    from 
        fb_requests
   where 
            user_id = 17 
        group by 
            game_selected
     having as_cnt < 4;

Upvotes: 1

javakid
javakid

Reputation: 11

I think its because of the aggregate function, try this.

Select game_selected 
from fb_requests
where user_id=17
group by game_selected
HAVING COUNT(accept_status) < 4;

Upvotes: 1

Omar Freewan
Omar Freewan

Reputation: 2678

You should use HAVING sql statement with aggregate functions
Try this 

Select game_selected from fb_requests
where user_id=17 
group by game_selected
having count(accept_status)<4;

Upvotes: 1

Related Questions