MYSQL count, group by and where clauses

Question

this works

SELECT COUNT(*) AS count ,transaction_id FROM loans_applicant  GROUP BY transaction_id

however i want to do this

SELECT COUNT(*) AS count ,transaction_id FROM loans_applicant  GROUP BY transaction_id WHERE count > 4

how can i do that ?

Change the where clause to a having clause.

Gordon Linoff
– Gordon Linoff

2015-09-16 11:12:11 +00:00
Commented Sep 16, 2015 at 11:12 — Gordon Linoff
– Gordon Linoff, Commented Sep 16, 2015 at 11:12
THANKS - how do i mark as the accepted answer ?

user1745467
– user1745467

2015-09-16 11:25:03 +00:00
Commented Sep 16, 2015 at 11:25 — user1745467
– user1745467, Commented Sep 16, 2015 at 11:25

Rahul · Accepted Answer · 2015-09-16 11:15:07Z

2

That condition should go into HAVING clause rather like below. Moreover, you have placed the WHERE in wrong position; it should come right after FROM clause.

SELECT COUNT(*) AS count ,
transaction_id 
FROM loans_applicant  
GROUP BY transaction_id 
HAVING COUNT(*) > 4;

answered Sep 16, 2015 at 11:15

Rahul

78.1k14 gold badges80 silver badges132 bronze badges

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

Comments

Zafar Malik · Accepted Answer · 2015-09-16 11:21:34Z

1

Follow order as per below-

where
group by
having
order by

In this way your query will be-

SELECT COUNT(*) AS count ,transaction_id 
FROM loans_applicant 
GROUP BY transaction_id
having count > 4;

answered Sep 16, 2015 at 11:21

Zafar Malik

6,8723 gold badges23 silver badges31 bronze badges

Comments

Suraj · Accepted Answer · 2016-07-13 12:55:54Z

1

You can do two things for that.
First : Use HAVING clause.

  SELECT COUNT(*) AS count ,transaction_id FROM loans_applicant  GROUP BY transaction_id having count(*) > 4

Second : Use Sub Query.

 SELECT count from (SELECT COUNT(*) AS count ,transaction_id FROM loans_applicant  GROUP BY transaction_id) where count > 4

answered Jul 13, 2016 at 12:55

Suraj

3,1954 gold badges32 silver badges50 bronze badges

Collectives™ on Stack Overflow

MYSQL count, group by and where clauses

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related