CODEWITHSUNDEEP
mysqlsql
stack
stack

Reputation: 10218

How can I count rows based on multiple different groups?

Here is my table structure:

// QandA
+----+----------------------------------------+------+---------+-----------+------------+
| Id |                   body                 | type | related | author_id | date_time  |
+----+----------------------------------------+------+---------+-----------+------------+
| 1  | content of question1                   | 0    | null    | 12345     | 1467468795 |
| 2  | content of first answer for question1  | 1    | 1       | 53456     | 1467469311 |
| 3  | content of question2                   | 0    | null    | 43634     | 1467469512 |
| 4  | content of second answer for question1 | 1    | 1       | 43665     | 1467470098 |
| 5  | content of first answer for question2  | 1    | 3       | 43324     | 1467471291 |
+----+----------------------------------------+------+---------+-----------+------------+
-- type: 0 means question, 1 means answer
-- related: null means question, else it's the id of its own question

I'm trying to implement this (something exactly like what stackoverflow does). An user can ask:

So I need to count the number of previous question for an user before letting him to ask. How can I do that?

Here is my query:

INSERT INTO QandA (id, body, type, related, author_id, date_tiem)
SELECT NULL, :body, 0, NULL, :id, unix_timestamp
FROM QANDA
WHERE author_id = :id AND
      type = 0 AND
      /* those three conditions */

Upvotes: 0

Views: 71

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269503

A simplification of @scaisEdge's solution removes the aggregation in the subqueries, uses scalar comparisons, and doesn't reference QandA in the outer query:

INSERT INTO QandA (id, body, type, related, author_id, date_time)
    SELECT NULL, :body, 0, NULL, :id, unix_timestamp
    FROM dual
    WHERE (select count(*)
           from QANDA  
           where date_time < unix_timestamp(DATE_SUB(now(), INTERVAL 90 minute))   and
                 author_id = :id and
                 type >= 1
          ) < 1
          (select count(*)
           from QANDA  
           where date_time < unix_timestamp(DATE_SUB(now(), INTERVAL 1 day)) and
                 author_id = :id and
                 type >= 1
          ) < 6
          author_id = :id AND type = 1 AND
          (select count(*)
           from QANDA  
           where date_time < unix_timestamp(DATE_SUB(now(), INTERVAL 1 month  ))   and
                 author_id = :id and
                 type >= 1
          ) < 50;

A more efficient version only does the aggregation once:

INSERT INTO QandA (id, body, type, related, author_id, date_time)
    SELECT NULL, :body, 0, NULL, :id, unix_timestamp
    FROM (select count(*) as num_month,
                 coalesce(sum(date_time < unix_timestamp(DATE_SUB(now(), INTERVAL 1 day)), 0) as num_day,
                 coalesce(sum(date_time < unix_timestamp(DATE_SUB(now(), INTERVAL 90 minute)), 0) as num_90min
          from QANDA  
          where date_time < unix_timestamp(DATE_SUB(now(), INTERVAL 1 month)) and
               author_id = :id and
               type >= 1
         ) a 
    WHERE num_month < 50 and num_day < 6 and num_90min < 1;

I also think this is easier to read.

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133360

Could be somethings lke this query 

INSERT INTO QandA (id, body, type, related, author_id, date_tiem)
SELECT NULL, :body, 0, NULL, :id, unix_timestamp
FROM QANDA as a 

WHERE author_id = :id 
AND type = 1 
AND a.author_id not in ( select  author_id = :id
                    from QANDA  
                    where date_time < unix_timestamp(DATE_SUB(now(),INTERVAL 90 minute  ))  
                    and  author_id = :id
                    and  type =>1
                    group by  author_id
                    having count(*) => 1  ) 
AND a.author_id not in ( select  author_id = :id
                    from QANDA  
                    where date_time < unix_timestamp(DATE_SUB(now(),INTERVAL 1 DAY   ))  
                    and  author_id = :id
                    and  type = 1
                    group by  author_id
                    having count(*) => 6  )     
AND a.author_id not in ( select  author_id = :id, count(*) 
                    from QANDA  
                    where date_time < unix_timestamp(DATE_SUB(now(),INTERVAL 1 MONTH   ))  
                    and  author_id = :id
                    and  type = 1
                    group by  author_id
                    having count(*) =>  50  )

Be careful with not in ..check also for empty result ..

Upvotes: 1

Related Questions