Question 88683 in Mysql Question posted in
The official MySQL documentation can be found here.

how to count records of group by column – Mysql

I have a notifications table which stores userID,isRead,type,message

UserID is id of user.
isRead stores 1/0 for read/unread.
Type stores the kind of notification, "mention/follow/reply".
message is message which the user will see.

I need a group by query that can return distinct "type" and number of unread messages for that type, but limit count to 1 record only.

How do I change the query below to get a resultset like the below:

Please note, number of unread messages for that TYPE can be more, but I want the query to stop counting as soon as first record is found. Then it starts looking for other TYPEs.

Will it be faster to run 3 separate queries, one for each TYPE?

select type from table where userID=123 and isRead=0 group by type

0=array(
  'type'=> mention,
  'count'=> 1
)
1=array(
  'type'=> reply,
  'count'=> 1
)
and so on
Tags:

2

Answers


  1. 0

    Presumably, you want something like:

    select type 
from mytable 
where userID = 123 
group by type
having min(isRead) = 0

    The query filters on the given user, groups by type, and selects only groups where at least one row is unread.

    For performance, the following index may help:

    mytable(userID, type, isRead)
    Login or Signup to reply.
  2. 0

    Something like this might help

    SELECT type FROM (
    SELECT type FROM table WHERE type = 'mention' AND userID = 123 AND isRead = 0 LIMIT 1
    UNION
    SELECT type FROM table WHERE type = 'follow' AND userID = 123 AND isRead = 0 LIMIT 1
    UNION
    SELECT type FROM table WHERE type = 'reply' AND userID = 123 AND isRead = 0 LIMIT 1
) AS X
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search