Conditional Count in SQL

Question

The following is my code

SELECT b.fulldate, 
       b.userid, 
       Count(a.isclanmatch) 
FROM  (SELECT fulldate, 
              realmatchid, 
              isclanmatch 
       FROM   gro_int.int_match 
       WHERE  ( fulldate BETWEEN '2013-06-30' AND Now() - 2 ) 
              AND isclanmatch = 1 
       GROUP  BY realmatchid)a 
      INNER JOIN gro_int.int_match_user b 
              ON b.realmatchid = a.realmatchid 
WHERE  ( b.fulldate BETWEEN '2013-06-30' AND Now() - 2 ) 
GROUP  BY userid

fulldate    userid  count(a.isclanmatch)
2013-07-09  1417    4
2013-07-15  1581    2
2013-06-30  1603    1

What I want to do is to only display the count of a.isclanmatch >=2. Is it possible?

Pavel Kutakov · Accepted Answer · 2013-07-29 08:43:34Z

3

Add

HAVING COUNT(a.isclanmatch)>=2

to the end of your query

answered Jul 29, 2013 at 8:43

Pavel Kutakov

9711 gold badge7 silver badges11 bronze badges

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

Comments

Xtygas · Accepted Answer · 2013-07-29 08:46:18Z

2

I think you want to do:

HAVING COUNT(a.isclanmatch)>=2

answered Jul 29, 2013 at 8:46

Xtygas

211 bronze badge

Comments

Arghya C · Accepted Answer · 2013-07-29 09:10:07Z

-1

Assuming your current query is fine, this should work

WITH mycte as(
SELECT b.fulldate, b.userid, COUNT(a.isclanmatch) colname
FROM(
    SELECT fulldate, realmatchid, isclanmatch
    FROM gro_int.int_match
    WHERE (fulldate BETWEEN '2013-06-30' AND NOW()-2) AND isclanmatch = 1
    GROUP BY realmatchid)a
        INNER JOIN gro_int.int_match_user b
    ON b.realmatchid = a.realmatchid
    WHERE (b.fulldate BETWEEN '2013-06-30' AND NOW()-2)
GROUP BY userid
)
select * from mycte where colname >= 2

answered Jul 29, 2013 at 9:10

Arghya C

10.1k4 gold badges51 silver badges70 bronze badges

2 Comments

Romesh Over a year ago

Why complexing a simple Query?

Arghya C Over a year ago

Yeah, agreed this is simple enough. I din't say just adding having doesn't work, but this IS an alternative approach and more flexible in cases he wants to select only a subset (few columns) of the resulting, do order by group by or other stuffs.

Collectives™ on Stack Overflow

Conditional Count in SQL

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related