Sql select query with count

Question

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.

what would you like to see? Please describe what you want to achive. — PeterRing
– PeterRing, Commented Jan 21, 2014 at 9:22
As a general rule, CREATE TABLE code (posted as text, with irrelevant columns omitted) and a few INSERT INTO queries with sample data are more useful than a picture of your data because it allows potential answerers to actually test the query. — Álvaro González
– Álvaro González, Commented Jan 21, 2014 at 9:24
I would like to select game_selected column based on accept_status, if accept_status count < 4 -- You want the accept_status count or the count of rows where accept_status =1 ? — grasshopper
– grasshopper, Commented Jan 21, 2014 at 9:33
@Dibish: can you place your table and data on SQL Fiddle? It would be easier to fetch the correct result on your available data. Or please provide your sample data in the form of insert query here which I can import. — ursitesion
– ursitesion, Commented Jan 21, 2014 at 11:19
@Dibish: Can you try the answer I have posted and give your feedback. It should work with every value of accept_status. — ursitesion
– ursitesion, Commented Jan 22, 2014 at 5:30

Omar Freewan · Accepted Answer · 2014-01-21 09:24:47Z

1

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;

answered Jan 21, 2014 at 9:24

Omar Freewan

2,6784 gold badges27 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dibeesh Over a year ago

Thanks for your help, its working, is there any way to take count(accept_status=1) only

Omar Freewan Over a year ago

@Dibish yes you can modify the WHERE statement to be like this where user_id=17 AND accept_status=1

Ravinder Reddy · Accepted Answer · 2014-01-21 09:28:34Z

1

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;

edited Jan 21, 2014 at 9:28

Ravinder Reddy

24.1k6 gold badges54 silver badges86 bronze badges

answered Jan 21, 2014 at 9:28

javakid

112 bronze badges

1 Comment

Dibeesh Over a year ago

Thank you so much for your quick reply, how can i select accept_status=1 records with the same query? I mean i want to take the count of accept_status=1 only

Yograj Sudewad · Accepted Answer · 2014-01-21 09:32:19Z

1

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;

answered Jan 21, 2014 at 9:32

Yograj Sudewad

3432 silver badges9 bronze badges

3 Comments

grasshopper Over a year ago

This doesn't work. Where clauses can only use existing columns or combinations (but not aggregations) of existing columns. OK I see you edited this and used having instead.

Dibeesh Over a year ago

@YograjSudewad SudewadThank you so much for your code, in your second query am not getting accept_status = 1 count

Yograj Sudewad Over a year ago

the second query counts only those records whose accept_status = 1 (for ex. you have 5 records from that 3 have accept_status = 1 and 2 have accept_status = 0 then that time its return sum as 3 because its count only accept_status = 1 records)

Sid M · Accepted Answer · 2014-01-21 11:23:29Z

1

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;

edited Jan 21, 2014 at 11:23

answered Jan 21, 2014 at 9:27

Sid M

4,3744 gold badges33 silver badges51 bronze badges

1 Comment

Dibeesh Over a year ago

Thank you so much for your quick reply, how can i select accept_status=1 records with the same query? I mean i want to take the count of accept_status=1 only

ursitesion · Accepted Answer · 2014-01-21 11:49:59Z

1

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.

answered Jan 21, 2014 at 11:49

ursitesion

9785 gold badges16 silver badges28 bronze badges

1 Comment

Dibeesh Over a year ago

Thanks for your answer. Not tried it. I will try it and let u know. Below answer worked for me that's why i didnt tried this answer. Thanks for the help

Aman Aggarwal · Accepted Answer · 2014-01-21 09:59:08Z

0

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

answered Jan 21, 2014 at 9:59

Aman Aggarwal

18.6k10 gold badges55 silver badges84 bronze badges

1 Comment

Dibeesh Over a year ago

Thank you so much for your quick reply, how can i select accept_status=1 records with the same query? I mean i want to take the count of accept_status=1 only

Collectives™ on Stack Overflow

Sql select query with count

6 Answers 6

2 Comments

1 Comment

3 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

1 Comment

3 Comments

1 Comment

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related