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?

score 3 · Answer 1 · answered Jul 29 '13 at 08:43

3

Add

HAVING COUNT(a.isclanmatch)>=2

to the end of your query

answered Jul 29 '13 at 08:43

Pavel Kutakov

971
1
7
11

score 2 · Answer 2 · answered Jul 29 '13 at 08:46

2

I think you want to do:

HAVING COUNT(a.isclanmatch)>=2

answered Jul 29 '13 at 08:46

Xtygas

21
1

score -1 · Answer 3 · answered Jul 29 '13 at 09:10

-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 '13 at 09:10

Arghya C

9,805
2
47
66

Why complexing a simple Query? – Romesh Jul 29 '13 at 09:28
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. – Arghya C Jul 29 '13 at 10:16

Conditional Count in SQL

3 Answers3